我们都知道group by 会选择保留第一条数据,默认是按照id排序的,如果我们想通过别的字段排序,比如最后创建的那条记录,可以先按照创建时间降序,再group by即可得到每个分组的最新创建的数据。
例:
通过如下sql查出两条数据
SELECT * FROM xxx WHERE groupId = 11 ORDER BY effectiveDate DESC
现在group by id 看下结果
SELECT a.* FROM ( SELECT * FROM xxx WHERE groupId = 11 ORDER BY effectiveDate desc ) a GROUP BY a.groupId
发现默认返回的是id升序后的第一个结果,这个结果明显不对的,因为子查询已经按照 effectiveDate 降序了,应该返回的是id为3的那条才对,其实是因为mysql 5.7版本对这个查询进行优化了,认为子查询中的order by可以进行忽略,只要Derived table里不包含如下条件就可以进行优化:
- UNION clause
- GROUP BY
- DISTINCT
- Aggregation
- LIMIT or OFFSET
解决办法:
通过limit 语句使子查询的order by生效
SELECT a.* FROM ( SELECT * FROM xxx WHERE groupId = 11 ORDER BY effectiveDate desc limit 111111111) a GROUP BY a.groupId
现在结果是我们期望的结果了。但是这必须保证limit的数量,所以也可以使用DISTINCT实现。