超级大白 发表于 2023-12-11 15:52:35

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 集字符串
SELECT task_id, SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', 5) log_ids
FROM t_task_exec_log
WHERE exec_status='success' AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)
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 条
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
页: [1]
查看完整版本: MySQL 分组排序后 → 如何取前N条或倒数N条