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的话需要换一换了，使用聚合函数取出你要的记录再关联原表获取第一条记录，或者使用组内排序生成行号后再按行号取第一条也行。

参考资料

• MySQL SQL GROUP BY是如何选择哪一条数据留下的? - 知乎
• 在SQL中，如何查询某一字段中最大值的数据？ - 知乎
• 185 Department Top Three Salaries
• mysql中,你们是如何处理 order by 和 group by 的? - 知乎
• SQL在分组查询时，怎么获取最新一条记录？ - rebiekong的回答 - SegmentFault 思否
• group by分组后获得每组中时间最大的那条记录 - 积累与沉淀 - CSDN博客
• 最近消息列表sql语句
• sql - Select first row in each GROUP BY group? - Stack Overflow
• mysql经验汇总
• MySQL 5.6 5.7 中组内排序的区别 - Yifans_Z Blog
• MySQL 组内排序 - 简书
• 这个需求能用一条 sql 完成吗？ - V2EX