MySQL - 按 DESC 按顺序分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4687827/
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 with Order by DESC
提问by ina
table: uuid, version, datetime
表：uuid、版本、日期时间
version is not unique, but the idea is to fetch only the rows with the latest datetime for a given uuid
version 不是唯一的，但其想法是仅获取给定 uuid 的最新日期时间的行
SELECT * FROM table WHERE uuid='bla' GROUP BY version ORDER BY datetime desc
SELECT * FROM table WHERE uuid='bla' GROUP BY version ORDER BY datetime desc
... of course gets datetime asc results -- is there a way to "preorder" the group by to desc, so that only the latest version is fetched?
...当然得到日期时间 asc 结果——有没有办法将组“预先排序”到 desc，以便只获取最新版本？
回答by The Scrum Meister
since the table only has those 3 field, and you are filtering by uid you can just use the MAX without the JOIN:
由于该表只有这 3 个字段，并且您正在按 uid 进行过滤，因此您可以只使用 MAX 而不使用 JOIN：
SELECT version, MAX(datetime) Maxdatetime
FROM table
WHERE uuid='bla'
GROUP BY version
However, if the table had more fields, or you are not filtering by uid- you need to first get the MAX datetime for each version, then select the row:
但是，如果表有更多字段，或者您没有过滤uid- 您需要首先获取每个版本的 MAX 日期时间，然后选择行：
SELECT t.uuid, t.version, t.datetime
FROM table t JOIN (
SELECT version, MAX(datetime) Maxdatetime
FROM table
WHERE uuid='bla'
GROUP BY version
) r ON t.version = r.version AND t.datetime = r.Maxdatetime
WHERE t.uuid='bla'
ORDER BY t.datetime desc
回答by bryan
SELECT * FROM
(SELECT * FROM table WHERE uuid='bla' ORDER BY datetime desc) table
GROUP BY version;
回答by azerafati
There is a better way for me, you could add a descto group by:
对我来说有一个更好的方法，你可以添加一个desc分组：
SELECT * FROM table WHERE uuid='bla' GROUP BY version desc
SELECT * FROM table WHERE uuid='bla' GROUP BY version desc
why it works is because my id's are generated and so always the latest id means the latest datetime
为什么它有效是因为我的 id 是生成的，所以最新的 id 总是意味着最新的日期时间
