MySQL默认开启了ONLY_FULL_GROUP_BY,也就是说SELECT中的列只能是GROUP BY中出现的列。
这样的话导致无法对分组后的数据进行筛选,必须在group by 中添加 select 中药查看的数据,这样又会有问题,所有优点矛盾。
so:
问题1:检索分组后时间最大 的数据,并且第一条数据要满足必要的条件
先查出没个组内最大时间最大,生成临时表:
(SELECT orderNo, max(createtime) AS max_time FROM dl_orderfollow GROUP BY orderNo) AS b
然后原表和临时表关联
WHERE a.orderNo=b.orderNo AND a.createtime=b.max_time and status='准备申请'
完整sql
SELECT a.* FROM
dl_orderfollow AS a,
(SELECT orderNo, max(createtime) AS max_time FROM dl_orderfollow GROUP BY orderNo) AS b
WHERE a.orderNo=b.orderNo AND a.createtime=b.max_time and status='准备申请'
参考:https://www.jianshu.com/p/717c4bdad462
问题2: 取分组后最新的前2条
SELECT a.*
from dl_orderfollow a
where (
SELECT count(1) from dl_orderfollow where a.orderNo=orderNo AND createTime < a.createTime
) > 2
order by orderNo,createTime
a.orderNo=orderNo --- 是确定分组
createTime < a.createTime
--确定排序条件,由于是最新的,则最新出现0次,第二新的出现1次,所以count(*)>2
标题:MySQL 组内排序,取分组后最新的 满足条件的第n条
作者:hymn
地址:https://dxyhymn.com/articles/2020/07/02/1593659353666.html