我有一个像这样的结果集:
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),然后:
ID | name | myvalue
4 | A4 | 33
5 | A5 | 33
IE 查询应该选择可用的最高“myvalue”(IE 33),并且应该删除 myvalue
SELECT ..... WHERE myvalue = THE_HIGHEST_OF(myvalue)
希望已经说清楚了...
先感谢您
edit:
我当前的查询是
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、任何数字...在最终结果集中,我只想包含具有最高可能相关性分数的行
我尝试过使用 GROUP BY,但我总是需要重复...
(very long code that returns a integer as relevance score) AS myvalue
...twice
SELECT * FROM t WHERE myValue IN (SELECT max(myValue) From t);
###请参阅此 SQLFiddle http://sqlfiddle.com/#!9/6135ad/1
Edit:
As per 与OP讨论。 https://chat.stackoverflow.com/rooms/16232/discussion-between-hims056-and-topeerornottopeerOP想用alias in WHERE
条款。但您只能在以下位置使用列别名GROUP BY
, ORDER BY
, or HAVING
条款。
看着这个答案。 https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error/942592#942592
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)