在做项目中遇到一个分组查询并取分组中最新的数据,MySQL查询时按照某个字段进行分组,查询结果默认获取分组中的第一条数据。现在需要进行子查询,也即是现进行对数据的排序再进行分组,但是分组内的排序不起作用。查找了相关资料,说是在MySQL5.6之后,sql执行时会有一个优化,就会把子句中的order by去掉。
sql如下:
--子查询排序不起作用
SELECT
ANY_VALUE(h.id) id, ANY_VALUE(h.creatorId) creatorId, ANY_VALUE(h.createTime) createTime, ANY_VALUE(h.deleted) deleted,
ANY_VALUE(h.targetHDFSPath) targetHDFSPath, ANY_VALUE(h.hdfsStorageAlias) hdfsStorageAlias
FROM (SELECT * FROM DataExtraction WHERE creatorId = '2' ORDER BY createTime DESC) h
GROUP BY h.targetHDFSPath
两种解决方案:
1、在子查询中使用limit
--子查询排序不起作用 在子查询中使用limit
SELECT
ANY_VALUE(h.id) id, ANY_VALUE(h.creatorId) creatorId, ANY_VALUE(h.createTime) createTime, ANY_VALUE(h.deleted) deleted,
ANY_VALUE(h.targetHDFSPath) targetHDFSPath, ANY_VALUE(h.hdfsStorageAlias) hdfsStorageAlias
FROM (SELECT * FROM DataExtraction WHERE creatorId = '2' ORDER BY createTime DESC LIMIT 100) h
GROUP BY h.targetHDFSPath
注意:这种查询方式限制了数据的数量,可以根据自己的实际情况使用,可以将limit的值设置的足够大。
2、在子查询中使用having
--子查询排序不起作用 在子查询中使用HAVING
SELECT
ANY_VALUE(h.id) id, ANY_VALUE(h.creatorId) creatorId, ANY_VALUE(h.createTime) createTime, ANY_VALUE(h.deleted) deleted,
ANY_VALUE(h.targetHDFSPath) targetHDFSPath, ANY_VALUE(h.hdfsStorageAlias) hdfsStorageAlias
FROM (SELECT * FROM DataExtraction WHERE creatorId = '2' HAVING 1=1 ORDER BY createTime DESC) h
GROUP BY h.targetHDFSPath
注意:这种看起来比较舒服对数据的获取没有限制,目前推荐这一个解决方案
在网上查找的说是内连接也可以达到与这两个相同的效果,具体待进一步探究。