MySQL先排序再分组查询

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

表设计

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

数据

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

查询

SELECT t.*
FROM (
  SELECT * FROM t1 HAVING 1 ORDER BY create_time DESC
)t
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