6
mysql Group by获取每个分组最新的记录
source link: https://callmesoul.cn/posts/578c61d0-4102-11eb-b113-9717c2aea740/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
mysql Group by获取每个分组最新的记录
mysql
2020-09-02 14:54:58
GROUP BY 查询默认是去每个分组第一条
很多时候我们需要获取每个分组的最后一条，例如查当前用户于其他用户的聊天记录的最后一条。
原始sql:
SELECT * FROM table GROUP BY type ORDER BY createdAt DESC
这里的ORDER BY是不生效的，因为GROUP BY 的执行会先于 ORDER BY
于是我们可以先排序后再来GROUP BY：
SELECT * FROM (SELECT * FROM table ORDER BY createdAt DESC) as resultGROUP BY result.type
大功告成！但是在某些mysql版本group by虽然不是拿了第一条，但也不是最后一条，奇了怪了。排查下，排序的查询是没问题，GROUP BY 后就出问题了。
后来百度以一段时间啊，好像有些兼容性，排序查询的时间要加上LIMIT就可以了
完整的正确写法:
SELECT * FROM (SELECT * FROM table ORDER BY createdAt DESC LIMIT 9999) as resultGROUP BY result.type
Nices!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK