自学内容网 自学内容网
自学内容网 > 正文

MySQL分组，获取组内最新的10条数据

🕗 发布于 2024-01-23 17:29 sql  数据库  mysql  

一、记录

记录一次SQL，最近在项目中遇到了一个相对比较复杂的SQL。
要求依据分组，获取每个分组后的前10条数据。
分组查询最新的数据，应该都做过，但是获取前10条数据，还是没处理过的。

二、处理

2.1 前期数据准备

新建一个测试表

CREATE TABLE `t_user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `ACCOUNT` varchar(50) DEFAULT NULL COMMENT '账号',
  `NAME` varchar(50) DEFAULT NULL COMMENT '姓名',
  `TIME` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

搞一批测试数据

INSERT INTO `t_user` (`ACCOUNT`, `NAME`, `TIME`) VALUES 
('zhangsan', '张三', '2024-01-01 00:00:00'),
('zhangsan', '张三', '2024-01-02 00:00:00'),
('zhangsan', '张三', '2024-01-03 00:00:00'),
('zhangsan', '张三', '2024-01-04 00:00:00'),
('zhangsan', '张三', '2024-01-05 00:00:00'),
('zhangsan', '张三', '2024-01-06 00:00:00'),
('zhangsan', '张三', '2024-01-07 00:00:00'),
('zhangsan', '张三', '2024-01-08 00:00:00'),
('zhangsan', '张三', '2024-01-09 00:00:00'),
('zhangsan', '张三', '2024-01-10 00:00:00'),
('zhangsan', '张三', '2024-01-11 00:00:00'),
('lisi', '李四', '2024-01-01 00:00:00'),
('lisi', '李四', '2024-01-02 00:00:00'),
('lisi', '李四', '2024-01-03 00:00:00'),
('lisi', '李四', '2024-01-04 00:00:00'),
('lisi', '李四', '2024-01-05 00:00:00'),
('lisi', '李四', '2024-01-06 00:00:00'),
('lisi', '李四', '2024-01-07 00:00:00'),
('lisi', '李四', '2024-01-08 00:00:00'),
('lisi', '李四', '2024-01-09 00:00:00'),
('lisi', '李四', '2024-01-10 00:00:00'),
('lisi', '李四', '2024-01-11 00:00:00'),
('wangwu', '王五', '2024-01-01 00:00:00'),
('wangwu', '王五', '2024-01-02 00:00:00'),
('wangwu', '王五', '2024-01-03 00:00:00'),
('wangwu', '王五', '2024-01-04 00:00:00'),
('wangwu', '王五', '2024-01-05 00:00:00'),
('wangwu', '王五', '2024-01-06 00:00:00'),
('wangwu', '王五', '2024-01-07 00:00:00'),
('wangwu', '王五', '2024-01-08 00:00:00'),
('wangwu', '王五', '2024-01-09 00:00:00'),
('wangwu', '王五', '2024-01-10 00:00:00'),
('wangwu', '王五', '2024-01-11 00:00:00');

数据如图，进行SQL验证

在这里插入图片描述

2.2 SQL

1、根据账号分组，时间倒叙排序，查询前10条数据

-- 根据账号分组，时间倒叙排序，查询前10条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME ASC 
) t_all
INNER JOIN (
SELECT max(s_id) s_id, ACCOUNT FROM (
SELECT (@i:=@i+1) AS s_id, s.* FROM t_user s, (SELECT @i:=0) s_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) s_all ON t_all.ACCOUNT = s_all.ACCOUNT AND t_all.t_id > s_all.s_id - 10
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

得到结果如下，没有出现 1 号的数据，说明SQL可用。
在这里插入图片描述

2.3 延伸

从上面的SQL可以看出来，是以一个基数，然后去截取前10个来做的。
这样的话，我们其实可以变更一下需求，将SQL改写一下。
即：
根据账号分组，时间倒叙排序，查询前10条数据中的，第1条数据。

第一条数据SQL如下：

-- 根据账号分组，时间倒叙排序，查询前10条数据中的第一条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME ASC 
) t_all
INNER JOIN (
SELECT max(a_id) a_id, ACCOUNT FROM (
SELECT (@a:=@a+1) AS a_id, a.* FROM t_user a, (SELECT @a:=0) a_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) a_all ON t_all.ACCOUNT = a_all.ACCOUNT AND t_all.t_id > a_all.a_id - 1
INNER JOIN (
SELECT max(b_id) b_id, ACCOUNT FROM (
SELECT (@b:=@b+1) AS b_id, b.* FROM t_user b, (SELECT @b:=0) b_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) b_all ON t_all.ACCOUNT = b_all.ACCOUNT AND t_all.t_id < b_all.b_id + 1
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

那么以此类推，第二条数据：

-- 根据账号分组，时间倒叙排序，查询前10条数据中的第二条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME ASC 
) t_all
INNER JOIN (
SELECT max(a_id) a_id, ACCOUNT FROM (
SELECT (@a:=@a+1) AS a_id, a.* FROM t_user a, (SELECT @a:=0) a_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) a_all ON t_all.ACCOUNT = a_all.ACCOUNT AND t_all.t_id > a_all.a_id - 2
INNER JOIN (
SELECT max(b_id) b_id, ACCOUNT FROM (
SELECT (@b:=@b+1) AS b_id, b.* FROM t_user b, (SELECT @b:=0) b_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) b_all ON t_all.ACCOUNT = b_all.ACCOUNT AND t_all.t_id < b_all.b_id 
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

第三条数据：

-- 根据账号分组，时间倒叙排序，查询前10条数据中的第三条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME ASC 
) t_all
INNER JOIN (
SELECT max(a_id) a_id, ACCOUNT FROM (
SELECT (@a:=@a+1) AS a_id, a.* FROM t_user a, (SELECT @a:=0) a_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) a_all ON t_all.ACCOUNT = a_all.ACCOUNT AND t_all.t_id > a_all.a_id - 3
INNER JOIN (
SELECT max(b_id) b_id, ACCOUNT FROM (
SELECT (@b:=@b+1) AS b_id, b.* FROM t_user b, (SELECT @b:=0) b_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) b_all ON t_all.ACCOUNT = b_all.ACCOUNT AND t_all.t_id < b_all.b_id - 1
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

第四条数据：

-- 根据账号分组，时间倒叙排序，查询前10条数据中的第四条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME ASC 
) t_all
INNER JOIN (
SELECT max(a_id) a_id, ACCOUNT FROM (
SELECT (@a:=@a+1) AS a_id, a.* FROM t_user a, (SELECT @a:=0) a_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) a_all ON t_all.ACCOUNT = a_all.ACCOUNT AND t_all.t_id > a_all.a_id - 4
INNER JOIN (
SELECT max(b_id) b_id, ACCOUNT FROM (
SELECT (@b:=@b+1) AS b_id, b.* FROM t_user b, (SELECT @b:=0) b_table ORDER BY ACCOUNT, TIME ASC 
) tmp GROUP BY ACCOUNT
) b_all ON t_all.ACCOUNT = b_all.ACCOUNT AND t_all.t_id < b_all.b_id - 2
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

三、总结

从上述SQL可以看出：

查询前10条数据，其实就是一个 大于号。
而后续的查询前10个数据中的第n条数据，也就是在原先的 大于号 的基础上，进行范围的缩小，增加一个 小于号 ，即通过框定查询结果的范围来得到想要的查询结果，SQL的体现上就是对于 t_id 的范围的框定。

四、其他

对于查询前10条数据中的第n条数据
以下是不同的写法，可以实现同样的效果，但是SQL却略微不同。
感兴趣的老铁可以自行研究研究

-- 根据账号分组，时间倒叙排序，查询前10条数据中的第一条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME DESC 
) t_all
INNER JOIN (
SELECT max(a_id) a_id, ACCOUNT FROM (
SELECT (@a:=@a+1) AS a_id, a.* FROM t_user a, (SELECT @a:=0) a_table ORDER BY ACCOUNT, TIME DESC 
) tmp GROUP BY ACCOUNT
) a_all ON t_all.ACCOUNT = a_all.ACCOUNT AND t_all.t_id > a_all.a_id - 11
INNER JOIN (
SELECT max(b_id) b_id, ACCOUNT FROM (
SELECT (@b:=@b+1) AS b_id, b.* FROM t_user b, (SELECT @b:=0) b_table ORDER BY ACCOUNT, TIME DESC 
) tmp GROUP BY ACCOUNT
) b_all ON t_all.ACCOUNT = b_all.ACCOUNT AND t_all.t_id < b_all.b_id - 9
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

-- 根据账号分组，时间倒叙排序，查询前10条数据中的第二条数据
SELECT t_all.* FROM (
SELECT (@s:=@s+1) AS t_id, t.* FROM t_user t, (SELECT @s:=0) t_table ORDER BY ACCOUNT, TIME DESC 
) t_all
INNER JOIN (
SELECT max(a_id) a_id, ACCOUNT FROM (
SELECT (@a:=@a+1) AS a_id, a.* FROM t_user a, (SELECT @a:=0) a_table ORDER BY ACCOUNT, TIME DESC 
) tmp GROUP BY ACCOUNT
) a_all ON t_all.ACCOUNT = a_all.ACCOUNT AND t_all.t_id > a_all.a_id - 10
INNER JOIN (
SELECT max(b_id) b_id, ACCOUNT FROM (
SELECT (@b:=@b+1) AS b_id, b.* FROM t_user b, (SELECT @b:=0) b_table ORDER BY ACCOUNT, TIME DESC 
) tmp GROUP BY ACCOUNT
) b_all ON t_all.ACCOUNT = b_all.ACCOUNT AND t_all.t_id < b_all.b_id - 8
ORDER BY t_all.ACCOUNT, t_all.TIME DESC;

参考链接：https://blog.csdn.net/wang1qqqq/article/details/117603407

OK，就这些吧。

有什么不对的还望指正，书写不易，觉得有帮助就点个赞吧！☺☺☺


原文地址：https://blog.csdn.net/qq_38254635/article/details/135509342

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024