MySQL mysql分组并排序每个组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4448504/
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 sort each group
提问by lachekar
I have the following table:
我有下表：
ID NAME TIME 1 A 0 2 A 3 3 B 1
I am using the query below which produces:
我正在使用下面的查询产生：
SELECT * FROM `table` GROUP BY `NAME`
ID NAME TIME 1 A 0 3 B 1
And I want use GROUP BYto generate a result like this (discount sort by the TIME column):
我想使用GROUP BY生成这样的结果（按时间列折扣排序）：
ID NAME TIME 2 A 3 3 B 1
回答by Jiri Kratochvil
SELECT NAME, MAX(TIME) as TIME
FROM table
GROUP BY time
ORDER BY time DESC
回答by php
select * from (select * from table order by TIME DESC) t group by NAME
回答by dreamluverz
Try this solution from here http://www.cafewebmaster.com/mysql-order-sort-group, it was able to solve my problem too :)
从这里尝试这个解决方案http://www.cafewebmaster.com/mysql-order-sort-group，它也能够解决我的问题:)
Sample:
样本：
SELECT * FROM
(
select * from `my_table` order by timestamp desc
) as my_table_tmp
group by catid
order by nid desc
回答by M Khalid Junaid
To get rows with highest time per group you could use a self join
要获得每组时间最长的行，您可以使用自联接
select a.*
from demo a
left join demo b on a.NAME =b.NAME and a.TIME < b.TIME
where b.NAME is null;
OR
或者
select a.*
from demo a
join (
select NAME, max(`TIME`) as `TIME`
from demo
group by NAME
) b on a.NAME =b.NAME and a.TIME = b.TIME;
回答by Skorpioh
Well, you have to decide what you want to see in the ID and the time fields after the group by. As an example I'll select the MAX(ID) and the SUM(time), then order by totaltime desc.
好吧，您必须决定要在 ID 和分组依据后的时间字段中看到什么。作为示例，我将选择 MAX(ID) 和 SUM(time)，然后按 totaltime desc 排序。
SELECT MAX(id), name, SUM(time) AS totaltime
FROM YourTableName
GROUP BY name
ORDER BY totaltime DESC
Hope this helps.
希望这可以帮助。
