$ cd /home/
python

MySQL组内排序问题：分组查询每组的前n条记录 - 简书

文章暂存

systemime
2021-02-20
13 min

摘要.

0.3642019.07.25 14:57:05 字数 1,350 阅读 11,161

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

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

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

最近，在做公司业务 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 中出现的列。那么，如何筛选到各订单更新时间最新的记录呢？

# 临时表关联查询

 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（即最新的记录）。

    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);

  2. id 与评论时间没有关系，id 大的评论时间可能不是最新：这种情况我们就需要使用 max(datachange_lasttime) 来获取最新的记录，但因为不同订单的时间有可能相同，因此还需要加多 order_id 这个条件去查询。

    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;

    SELECT * FROM o_policy_orderrelation o WHERE datachange_lasttime = (SELECT MAX(datachange_lasttime) FROM o_policy_orderrelation GROUP BY orderid HAVING orderid = o.orderid);

    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);

    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(*)

 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;

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;

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，按照数据的逻辑存储顺序来返回。

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

https://www.jianshu.com/p/717c4bdad462 https://www.jianshu.com/p/717c4bdad462

上次编辑于: 5/20/2021, 7:26:49 AM
  • 临时表关联查询
  • 子查询
  • JOIN + COUNT(*)
  • SQL 变量
  • 使用 groupconcat() & substringindex() 函数
  • 开窗函数
  • 窗口分析函数
  • 组内排序
© Systemime 2018-2021
Powered by VuePress & Gungnir