MySQL SQL：查找每组的最大记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2657482/
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
SQL: Find the max record per group
提问by user319088
Possible Duplicate:
Retrieving the last record in each group
可能的重复：
检索每组中的最后一条记录
I have one table, which has three fields and data.
我有一张表，其中包含三个字段和数据。
Name , Top , Total cat , 1 , 10 dog , 2 , 7 cat , 3 , 20 horse , 4 , 4 cat , 5 , 10 dog , 6 , 9
I want to select the record which has highest value of Totalfor each Name, so my result should be like this:
我想Total为 each选择具有最高值的记录Name，所以我的结果应该是这样的：
Name , Top , Total cat , 3 , 20 horse , 4 , 4 Dog , 6 , 9
I tried group by name order by total, but it give top most record of group by result. Can anyone guide me, please?
我尝试按名称顺序按总数分组，但它给出了按结果分组的最高记录。请问有人可以指导我吗？
回答by Tomalak
select
Name, Top, Total
from
sometable
where
Total = (select max(Total) from sometable i where i.Name = sometable.Name)
or
或者
select
Name, Top, Total
from
sometable
inner join (
select max(Total) Total, Name
from sometable
group by Name
) as max on max.Name = sometable.Name and max.Total = sometable.Total
回答by Adriaan Stander
You can try something like
你可以尝试类似的东西
SELECT s.*
FROM sometable s INNER JOIN
(
SELECT Name,
MAX(Total) MTotal
FROM sometable
GROUP BY Name
) sMax ON s.Name = sMax.Name
AND s.Total = sMax.MTotal
回答by Claudia
Or using an Exists clause, wich returns the only row that exists in both tables
或者使用 Exists 子句，返回两个表中唯一存在的行
SELECT * from sometable T
where exists
(select 1
from (SELECT nombre, max(total) as total FROM sometable TT
GROUP by nombre) TT
where T.name=TT.name
and T.total=TT.total
)
