sql/mysql筛选器，仅包含最大值

本文介绍了sql/mysql筛选器，仅包含最大值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个这样的结果集:

I have a resultset that is like this:

ID | name | myvalue 1 | A1 | 22 2 | A2 | 22 3 | A3 | 21 4 | A4 | 33 5 | A5 | 33 6 | A6 | 10 7 | A7 | 10 8 | A8 | 10 9 | A9 | 5

我想要的是仅包含包含最高可用"myvalue"(在前面的示例中为33)的行，然后:

what i want, is to include only rows that contains the highest "myvalue" available (in the previous example is 33), then:

ID | name | myvalue 4 | A4 | 33 5 | A5 | 33

IE，查询应选择可用的最高"myvalue"(IE 33)，并且应删除myvalue< 33

IE the query should pick the highest "myvalue" available (IE 33) and it should remove the rows that have myvalue < 33

SELECT ..... WHERE myvalue = THE_HIGHEST_OF(myvalue)

希望一切都清楚...

Hoping to have been clear...

先谢谢您

我当前的查询是

SELECT *, (very long code that returns a integer as relevance score) AS myvalue FROM mytable HAVING myvalue = ????? ORDER BY myvalue DESC

现在，最高myvalue可以是10、20、30，任何数字...在最终结果集中，我只想包含相关性得分最高的行

now the highest myvalue can be 10, 20, 30, any number... in the final resultset i want to include only the rows that have the highest possible relevance score

我尝试使用GROUP BY，但是我总是需要重复...

ive tried using GROUP BY, but i always need to repeat the...

(very long code that returns a integer as relevance score) AS myvalue

...两次

推荐答案

SELECT * FROM t WHERE myValue IN (SELECT max(myValue) From t);

请参阅此SQLFiddle

根据与OP进行的讨论. OP希望在WHERE子句中使用 alias .但是您只能在GROUP BY，ORDER BY或HAVING子句中使用列别名. 查看此答案

As per discussion with OP. OP wants to use alias in WHERE clause. But you can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses. Look at this answer.