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

短命女 2024-04-24 14:51 40阅读 0赞

#### 一、记录 ####

记录一次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验证

![在这里插入图片描述][9ff65e11ce1940e7941b093a2348500d.png]

###### 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可用。  
![在这里插入图片描述][b9272d20b52345b99ac1acfb45d89fe1.png]

###### 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，就这些吧。

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

[9ff65e11ce1940e7941b093a2348500d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/f9d690f73cc7496098cf29f1c83d6033.png
[b9272d20b52345b99ac1acfb45d89fe1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/f9fada50ed79486c8a18e75a019e8380.png