MySQL组内排序问题:分组查询每组的前n条记录

在开发中经常遇到这样一类需求:取每种类型排名前几的数据,在此我简称它为组内排序。比如:

  • 检索论坛中某一版块所有主题的最新一条帖子
  • 查找所有会话中最新一条消息
  • 查找一类商品的最新报价

这类问题的共同点是:需要按某个字段分组,且每组只能取一条记录;按某个字段倒序。

最近,在做公司业务SQL查询时,遇到一个有趣的问题:获取各个订单下更新时间最新的一条记录。每个订单每更新一次,都会根据更新内容生成一条新的记录。

举例来说,有这样一个表:

CREATE TABLE o_policy_orderrelation ( 
    id bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
    orderid bigint NOT NULL default '0' COMMENT '订单号',
    eventid bigint NOT NULL default '0' COMMENT '公共事件id',
    emergencyordertype tinyint NOT NULL default '0' COMMENT '应急订单类型;1-行中受影响,2-即将出行受影响,3-即将出行不受影响',
    datachange_lasttime timestamp(3)  NOT NULL default CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    policyid bigint NOT NULL default '0' COMMENT '政策id',
    eventpolicyid bigint NOT NULL default '0' COMMENT '事件政策id',
    PRIMARY KEY (id), KEY ix_orderid (orderid), KEY ix_eventid (eventid), KEY ix_DataChange_LastTime (datachange_lasttime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='订单事件关联表';

数据库中记录如下:

id | orderid | eventid | emergencyordertype | datachange_lasttime | policyid | eventpolicyid
19 | 3062774559 | 2 | 0 | 2019-07-17 14:56:13.361 | 0 | 0
20 | 3062776520 | 2 | 0 | 2019-07-17 14:56:13.362 | 0 | 0
21 | 3062776519 | 2 | 0 | 2019-07-17 14:56:13.363 | 0 | 0
22 | 3062776517 | 2 | 0 | 2019-07-17 14:56:13.409 | 0 | 0
23 | 3062776515 | 2 | 0 | 2019-07-17 14:56:13.410 | 0 | 0
24 | 3062776514 | 2 | 1 | 2019-07-17 14:56:38.477 | 1 | 3
25 | 3062776514 | 2 | 2 | 2019-07-17 14:56:39.717 | 2 | 4
26 | 3062776514 | 2 | 3 | 2019-07-17 14:56:41.128 | 3 | 5
27 | 3062776727 | 111 | 1 | 2019-07-17 20:12:21.698 | 1 | 6

为了解决这个问题,我想到了使用group by进行订单ID分组。然后在查询时,筛选出更新时间时间戳最大的数据。SQL语句如下:

SELECT orderid, MAX(datachange_lasttime) AS max_time, eventpolicyid FROM o_policy_orderrelation GROUP BY orderid;

查询结果如下:

id | orderid | max_time | eventpolicyid
19 | 3062774559 | 2019-07-17 14:56:13.361 | 0
24 | 3062776514 | 2019-07-17 14:56:41.128 | 3
23 | 3062776515 | 2019-07-17 14:56:13.410 | 0
22 | 3062776517 | 2019-07-17 14:56:13.409 | 0
21 | 3062776519 | 2019-07-17 14:56:13.363 | 0
20 | 3062776520 | 2019-07-17 14:56:13.362 | 0
27 | 3062776727 | 2019-07-17 20:12:21.698 | 6

我们可以看到orderid为3062776514的订单在数据库中有对应的三条记录,主键id分别为24,25,26。按道理我们查询的该订单ID对应最近一条记录是id=26,然而查询出来对应的记录是id=24的eventpolicyid,即使max_time是最新的。

使用这条SQL确实帮我筛选到了各订单最新的更新时间,但是无法筛选出最新更新时间对应的记录(其他列的值是随机取的一条,具体选择策略后面会介绍),因为MySQL默认开启了ONLY_FULL_GROUP_BY,也就是说SELECT中的列只能是GROUP BY中出现的列。那么,如何筛选到各订单更新时间最新的记录呢?

解决办法

临时表关联查询

# [sql:查询每个类别最新发表的那条记录 - 个人文章 - SegmentFault 思否](https://segmentfault.com/a/1190000015864190)
SELECT a.* FROM o_policy_orderrelation AS a, (SELECT orderid, max(datachange_lasttime) AS max_time FROM o_policy_orderrelation GROUP BY orderid) AS b
WHERE a.orderid=b.orderid AND a.datachange_lasttime=b.max_time;

先查出每个orderid的最大的datachange_lasttime,查询生成一个临时表b:(SELECT orderid, max(datachange_lasttime) AS datachange_lasttime FROM o_policy_orderrelation GROUP BY orderid) AS b,然后原表a与临时表b关联查询即可:WHERE a.orderid=b.orderid and a.datachange_lasttime=b.datachange_lasttime

子查询

如何才能对group by分组内的数据进行排序了,这个需要根据不同的需求处理。

  1. 数据库表设置了自增主键:主键id最大的,更新时间肯定也是最新的。这种情况我们可以使用id代替时间去搜寻并组内排序,使用max(id)就可以获取到每个分组中最大的记录id(即最新的记录)。
# 查询orderid分组中最大id对应的记录
SELECT t2.max_id, t1.* FROM o_policy_orderrelation t1, (SELECT max(id) AS max_id FROM o_policy_orderrelation GROUP BY orderid) t2 WHERE t1.id = t2.max_id;

SELECT * FROM o_policy_orderrelation WHERE id IN (SELECT max(id) FROM o_policy_orderrelation GROUP BY orderid);
  1. id与评论时间没有关系,id大的评论时间可能不是最新:这种情况我们就需要使用max(datachange_lasttime)来获取最新的记录,但因为不同订单的时间有可能相同,因此还需要加多order_id这个条件去查询。
# right join方式:使用right join可以减少外层的数据集。使用where orderid is not null可以使group by orderid时使用索引。
# [mysql group by 组内排序方法 - 傲雪星枫 - CSDN博客](https://blog.csdn.net/fdipzone/article/details/72453553)
SELECT a.* FROM o_policy_orderrelation AS a RIGHT JOIN 
(SELECT orderid, max(datachange_lasttime) AS max_time FROM o_policy_orderrelation WHERE orderid IS NOT NULL GROUP BY orderid) AS b 
ON a.orderid=b.orderid AND a.datachange_lasttime=b.max_time;

# GROUP BY HAVING方式
# [Group by ID having MAX(date) problem - Databases - The SitePoint Forums](https://www.sitepoint.com/community/t/group-by-id-having-max-date-problem/3765)
SELECT * FROM o_policy_orderrelation o
WHERE datachange_lasttime = (SELECT MAX(datachange_lasttime)
                              FROM o_policy_orderrelation
                              GROUP BY orderid
                              HAVING orderid = o.orderid);

# [mysql多表查询及其 group by 组内排序 - 北斗极星 - 博客园](https://www.cnblogs.com/hubing/p/4831836.html)
SELECT id, orderid, datachange_lasttime, eventpolicyid FROM o_policy_orderrelation AS t 
GROUP BY id, orderid, datachange_lasttime, eventpolicyid 
HAVING datachange_lasttime=(SELECT max(datachange_lasttime) FROM o_policy_orderrelation WHERE orderid=t.orderid);

# [这个需求能用一条 sql 完成吗? - V2EX](https://www.v2ex.com/t/382102)
SELECT * FROM o_policy_orderrelation t WHERE NOT EXISTS (SELECT * FROM o_policy_orderrelation WHERE orderid = t.orderid AND datachange_lasttime > t.datachange_lasttime);

JOIN + COUNT(*)

# [MySQL分组top N问题疑点 - 知乎](https://zhuanlan.zhihu.com/p/66777046)
# 如果需要取TopN,只需要把<1改为<n
SELECT * FROM o_policy_orderrelation AS a
WHERE (
    SELECT count(*) FROM o_policy_orderrelation AS b WHERE a.orderid=b.orderid AND a.datachange_lasttime < b.datachange_lasttime
) < 1;

# [MySQL学习笔记:三种组内排序方法 - Hider1214 - 博客园](https://www.cnblogs.com/hider/p/9087374.html)
# [【原】MySQL分组排序(包含组内排名、求中位数) - MERRU - 博客园](https://www.cnblogs.com/merru/articles/4626045.html)
# 如果还需要组内排序显示TopN的话,只用删除HAVING子句就行了
SELECT a.id, a.orderid, a.datachange_lasttime, a.eventpolicyid, count(*) AS rank
FROM o_policy_orderrelation a
JOIN o_policy_orderrelation b ON a.orderid=b.orderid AND a.datachange_lasttime <= b.datachange_lasttime
GROUP BY a.orderid, a.datachange_lasttime HAVING rank=1
ORDER BY a.orderid, a.datachange_lasttime DESC;

# [MySQL实现over partition by(分组后对组内数据排序) - MrCao杰罗尔德的博客 - CSDN博客](https://blog.csdn.net/m0_37797991/article/details/80511855)
SELECT *
FROM (SELECT
        t.orderid,
        t.datachange_lasttime,
        COUNT(*) AS rank
      FROM o_policy_orderrelation t
        LEFT JOIN o_policy_orderrelation r
          ON t.orderid = r.orderid
            AND t.datachange_lasttime = r.datachange_lasttime
      GROUP BY t.orderid) s
WHERE s.rank = 1;

SQL变量

SET @num := 0, @type := ''; 
SELECT eventid, 
       orderid, 
       policyid,
       datachange_lasttime 
FROM   (SELECT *, 
               @num := IF(@type = orderid, @num + 1, 1) AS row_number, 
               @type := orderid                         AS temp_type 
        FROM   o_policy_orderrelation 
        ORDER  BY orderid, 
                  datachange_lasttime DESC) AS a 
WHERE  a.row_number <= 1;

使用 group_concat() & substring_index() 函数

注意一点:group_concat的字段是int型时需要转成字符型,否则可能出现结果不对的情况

select group_concat(orderid) id_list, name from ttt group by name; 分组取出所有orderid,逗号分隔
select group_concat(orderid order by orderid SEPARATOR '_') from ttt group by name; 分组取出所有id,逗号分隔并排序,自定义分隔符"_"
select substring_index(group_concat(orderid order by orderid SEPARATOR '_'),'_',2) from ttt group by name; 分组取出所有id,逗号分隔并排序取前两个,自定义分隔符"_"

SELECT * FROM o_policy_orderrelation WHERE id IN(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY datachange_lasttime DESC),',',1) FROM o_policy_orderrelation GROUP BY orderid) ORDER BY datachange_lasttime DESC;

开窗函数

表 A 和表 B,为 1 对多关系,如何查询对于某个 A,多个 B 中最近更新的那一条记录。
先把 B 表分组排序,row_number() over(partition by xx order by 时间) as 'rk',然后 select * from B 排序后的表,where rk =1,再将其与 A 表关联。

SQL 在分组查询时,怎么获取最新一条记录 - V2EX

窗口分析函数

SELECT t.id,t.orderid,t.datachange_lasttime 
FROM(
SELECT id,orderid,datachange_lasttime,RANK() OVER (PARTITION BY orderid ORDER BY datachange_lasttime DESC) RK
FROM o_policy_orderrelation) t
WHERE t.RK<2
select t1.* from
(select (@rowNum1:=@rowNum1+1) as rowNo, id, orderid, datachange_lasttime, policyid from o_policy_orderrelation a, (Select (@rowNum1 :=0)) b order by a.orderid,a.datachange_lasttime desc) t1 left join
(select (@rowNum2:=@rowNum2+1) as rowNo, id, orderid, datachange_lasttime, policyid from o_policy_orderrelation c, (Select (@rowNum2 :=1)) d order by c.orderid,c.datachange_lasttime desc) t2 on t1.rowNo=t2.rowNO
where t1.orderid<>t2.orderid or t2.orderid is null

mysql下实现窗口分析函数 - q195136130的专栏 - CSDN博客
MySQL实现over partition by(分组后对组内数据排序) - MrCao杰罗尔德的博客 - CSDN博客

无效的错误查询语句示例

组内排序

先在派生表中排序,得出结果后再分组,从而实现了组内排序。但是在 5.7 中,首先需要关闭 sql_mode = ONLY_FULL_GROUP_BY;相同的 name 值,返回则是取了最早写入的数据行,忽略了order by no desc,按照数据的逻辑存储顺序来返回。

# [踩坑杂谈二:MYSQL分组组内排序 - - ITeye博客](https://jdkleo.iteye.com/blog/2393834)
SELECT *
FROM (SELECT * FROM o_policy_orderrelation ORDER BY orderid, datachange_lasttime DESC) AS t
GROUP BY t.orderid ORDER BY datachange_lasttime DESC;

然而,5.7以后对排序的sql解析做了优化,子查询中的排序是会被忽略的。5.6这样写是没问题的,5.7的话需要换一换了,使用聚合函数取出你要的记录再关联原表获取第一条记录,或者使用组内排序生成行号后再按行号取第一条也行。

参考资料

推荐阅读更多精彩内容