BlackTeaChan's Blog

MySQL先排序再分组查询

发表于 分类于 ， 阅读次数：

通常情况下，MySQL同时使用ORDER BY与GROUP BY只能实现分组后排序，但如果想要实现先排序再分组，就需要施加一点特殊手段

表设计

1
CREATE TABLE `t1` (
2
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
3
  `type` tinyint(2) NOT NULL COMMENT '类型',
4
  `name` varchar(50) NOT NULL COMMENT '名称',
5
  `create_time` datetime NOT NULL COMMENT '创建时间',
6
  PRIMARY KEY (`id`)
7
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据

1
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (1, 1, '1-1', '2025-01-01 01:00:00');
2
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (2, 1, '1-2', '2025-01-01 02:00:00');
3
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (3, 1, '1-3', '2025-01-01 03:00:00');
4
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (4, 2, '2-1', '2025-01-02 01:00:00');
5
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (5, 2, '2-2', '2025-01-02 02:00:00');
6
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (6, 2, '2-3', '2025-01-02 03:00:00');
7
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (7, 3, '3-1', '2025-01-03 01:00:00');
8
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (8, 3, '3-2', '2025-01-03 02:00:00');
9
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (9, 3, '3-3', '2025-01-03 03:00:00');
10
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (10, 4, '4-1', '2025-01-04 01:00:00');
11
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (11, 4, '4-2', '2025-01-04 02:00:00');
12
INSERT INTO `t1` (`id`, `type`, `name`, `create_time`) VALUES (12, 4, '4-3', '2025-01-04 03:00:00');

查询

1
SELECT t.*
2
FROM (
3
  SELECT * FROM t1 HAVING 1 ORDER BY create_time DESC
4
)t
5
GROUP BY t.type;

结果

id type name create_time
3 1 1-3 2025-01-01 03:00:00
6 2 2-3 2025-01-02 03:00:00
9 3 3-3 2025-01-03 03:00:00
12 4 4-3 2025-01-04 03:00:00
如果文章对您有帮助，欢迎评论或打赏，感谢支持！