在开发中经常遇到这样一类需求:取每种类型排名前几的数据,在此我简称它为组内排序。比如:
这类问题的共同点是:需要按某个字段分组,且每组只能取一条记录;按某个字段倒序。
最近,在做公司业务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分组内的数据进行排序了,这个需要根据不同的需求处理。
数据库表设置了自增主键:主键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);
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
# [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 表关联。
窗口分析函数
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