MySQL GROUP BY 和 HAVING
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5112590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL GROUP BY and HAVING
提问by Elie
I'm grouping my results based on a column X and I want to return the rows that has highest Column Y's value in the group.
我根据 X 列对结果进行分组，并且我想返回组中 Y 列值最高的行。
SELECT *
FROM mytable
GROUP BY col1
HAVING col2 >= (SELECT MAX(col2)
FROM mytable AS mytable2
WHERE mytable2.col1 = mytable.col1 GROUP BY mytable2.col1)
I want to optimize the query above. Is it doable without sub-queries?
我想优化上面的查询。没有子查询是否可行？
I found the solution and it's simpler than you think:
我找到了解决方案，它比您想象的要简单：
SELECT * FROM (SELECT * FROM mytable ORDER BY col2 DESC) temp GROUP BY col1
Runs in 5 milliseconds on 20,000 rows.
在 5 毫秒内在 20,000 行上运行。
回答by OMG Ponies
Using a derived table/inline view for a JOIN:
为 JOIN 使用派生表/内联视图：
SELECT x.*
FROM mytable x
JOIN (SELECT t.col1,
MAX(t.col2) AS max_col2
FROM MYTABLE t
GROUP BY t.col1) y ON y.col1 = x.col1
AND y.max_col2 >= x.col2
Be aware that this will duplicate xrecords if there's more than one related yrecord. To remove duplicates, use DISTINCT:
请注意，x如果有多个相关y记录，这将复制记录。要删除重复项，请使用DISTINCT：
SELECT DISTINCT x.*
FROM mytable x
JOIN (SELECT t.col1,
MAX(t.col2) AS max_col2
FROM MYTABLE t
GROUP BY t.col1) y ON y.col1 = x.col1
AND y.max_col2 >= x.col2
The following is untested, but will not return duplicates (assuming valid):
以下内容未经测试，但不会返回重复项（假设有效）：
SELECT x.*
FROM mytable x
WHERE EXISTS (SELECT NULL
FROM MYTABLE y
WHERE y.col1 = x.col1
GROUP BY y.col1
HAVING MAX(y.col2) >= x.col2)
回答by Fisherman
Your Col2 never be > then MAX(col2) so i suggest to use col2 = MAX(col2)
你的 Col2 永远不会 > then MAX(col2) 所以我建议使用 col2 = MAX(col2)
so HERE is the QUERY
所以这里是查询
SELECT * FROM mytable GROUP BY col1 HAVING col2 = MAX( col2 )
