MySQL - 使用 GROUP BY 和 DESC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7306082/
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 - using GROUP BY and DESC
提问by user1946705
In my SQL query I am selecting data with GROUP BYand ORDER BYclauses. The table has the same numbersacross multiple rows with different times in each row. So I think I want to apply a GROUP BYclause.
在我的 SQL 查询中，我使用GROUP BY和ORDER BY子句选择数据。该表numbers在多行中具有相同的内容，每行中的时间不同。所以我想我想应用一个GROUP BY子句。
However in the results return the oldest time with the number, but I need the most recent time.
但是在结果中用数字返回最旧的时间，但我需要最近的时间。
SELECT * FROM TABLE GROUP BY (numbers) ORDER BY time DESC
The query appears as if it should first apply GROUP BYand then ORDER BY... but the results do not appear to work this way.
查询看起来好像应该先应用GROUP BY然后应用ORDER BY...但结果似乎不是这样工作的。
Is there any way to fix this?
有没有什么办法解决这一问题？
回答by Karolis
SELECT *
FROM table t
WHERE time = (
SELECT max(time)
FROM table
WHERE t.numbers = numbers
)
回答by jbrond
work-around is to re-write the query as:
解决方法是将查询重写为：
SELECT * FROM (SELECT * FROM table ORDER BY time DESC) AS t GROUP BY numbers;
回答by B. Bohdan
SELECT * FROM table
WHERE time IN (
SELECT MAX(time)
FROM table
GROUP BY numbers
)
回答by Betty Mock
According to the manual you can add desc to the group by list:
Example:
group by item1, item2 desc, item3
根据手册，您可以将 desc 添加到 group by 列表中：例如：
group by item1, item2 desc, item3
with or without rollup.
有或没有汇总。
I've tried this and it works in Ubuntu version 5.5.58. The reference page is: https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html
我试过这个，它在 Ubuntu 5.5.58 版中工作。参考页面为：https: //dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html
回答by Harshad
SELECT * FROM TABLE GROUP BY numbers DESC;
This will give you last record from group.
这将为您提供组中的最后一条记录。
Thanks
谢谢
