MySQL 分组排序后 → 如何取前N条或倒数N条

超级大白

开心一刻

　　晚上，老婆辅导女儿写语文作业
　　填空题：春天来了，__绿了
　　女儿：春天来了，爸爸绿了
　　老婆一脸不悦地问道：你再说一遍，春天来了，什么玩意绿了？
　　女儿：春天来了，爸爸绿了呀
　　老婆很生气，但依旧温柔地问道：春天来了，爸爸怎么绿了呢
　　女儿略带哭腔的说道：那冬天呢
　　老婆急的直挠头：冬，冬，冬...，它跟天气有什么关系啊，那春天来了不应该是小草绿了吗
　　女儿疑惑的左看右看，问道：那爸爸啥时候绿？
　　老婆气的把笔一甩：我不知道你爸啥时候绿，你问你爸
　　女儿转头看向我，问道：爸爸，你啥时候绿？
　　我心里咯噔一下，这小棉袄有点漏风呀，小心翼翼的看了一眼老婆，坚定地说道：爸爸是不会绿的！
前情回顾

　　前两天翻自己的博客的时候，翻到了：记一次有意思的 SQL 实现 → 分组后取每组的第一条记录
　　突然意识到好像有续集没写
　　翻到结尾，果然有个留疑

　　但我要强调一点：这是我给你们的留疑，并不是我给你们的承诺！
　　我没写续集，你们可不能生气，实在是生气，那你来打我呀！

　　分组后取第一条记录

　　我们先来简单回顾下实现方式
　　1、循环查数据库
　　　　逻辑很清晰，实现起来也很简单，但是会循环查数据库，开发规范一般会明确禁止这种写法
　　2、 GROUP BY 结合 MySQL 函数 
　　　　 GROUP BY 之后，用 GROUP_CONCAT(log_id ORDER BY data_date DESC,modify_time DESC) 对 log_id 进行拼接
　　　　然后用 SUBSTRING_INDEX 函数截取第一个 log_id 
　　　　最后 INNER JOIN 
　　　　但是， GROUP_CONCAT 有长度限制的问题，默认 1024 个字节（ show variables like 'group_concat_max_len'; ）
　　3、新增最新记录表
　　　　专门用一张表来记录任务最新执行成功记录
　　　　表数据维护的逻辑：不存在则插入，存在则更新（记录不存在则插入，存在则更新 → MySQL 的实现方式有哪些？）
取前N条或倒数N条

　　我们回到标题，分组排序后，如何取前N条记录或倒数N条记录
　　循环查数据库

　　1、先批量查询 task_id 
　　2、再根据 task_id 逐个去查 t_task_exec_log ，排序获取前N条记录
　　3、最后进行一个数据汇合，封装成页面需要的数据格式
　　但这种方式会循环查数据库，一般是被禁止的
　　GROUP BY 结合 MySQL 函数

　　1、先批量查询 task_id 
　　2、再根据这些 task_id 从 t_task_exec_log 批量查询每个任务的前N条记录的 log_id 集字符串

1. SELECT task_id, SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', 5) log_ids
2. FROM t_task_exec_log
3. WHERE exec_status='success' AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)
4. GROUP BY task_id;

复制代码

View Code

　　 SUBSTRING_INDEX(str, delim, count) 不做过多介绍，具体可翻阅：SUBSTRING_INDEX
　　 count 参数可以用来实现前N条或倒数N条
　　比如前 5 条： SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', 5) 

　　倒数 5 条： SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', -5) 

　　3、log_ids 按逗号（,）进行拆分得到 log_id 列表，然后根据 log_id 列表从 t_task_exec_log 批量查询
　　4、最后进行数据汇合，封装成页面需要的数据格式
　　但 GROUP_CONCAT 长度限制是需要考虑的点
　　新增最新记录表

　　这种方式比较契合只取第一条的情况，不适合取N条的情况
　　N不固定，这张表的存储数据范围就不好确定
　　如果为了全兼容的话，那这张表就成了 t_task_exec_log ，那就没意义了
　　窗口函数

　　 MySQL8 新增的特性
　　关于窗口函数可查阅官方文档：Window Functions，不做过多介绍
　　我们用 ROW_NUMBER 来实现 取前N条或倒数N条 
　　1、批量查询 task_id 
　　2、使用 ROW_NUMBER ，取前N条或倒数N条
　　取第一条

　　结果如下

　　取前 5 条

[code]SELECT * from (    SELECT *, ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY data_date DESC, modify_time DESC) AS rn    FROM t_task_exec_log     WHERE exec_status='success'     AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)) t WHERE rn