在我们日常工作中,会有这样的需求,假设一张学生表有主键、姓名、学号、年龄、创建时间等字段,但是我们有个需求就是:根据学号分组且每条数据都是创建时间为最新的数据
表结构如下:
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `password` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` char(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `idCard` varchar(100) DEFAULT NULL COMMENT '学号', `time` datetime DEFAULT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
实现思路1:
- 子查询
(SELECT 统一信用代码, MAX(更新时间) AS max_time FROM your_table GROUP BY 统一信用代码)
用于找出每个统一信用代码的最新时间。 - 主查询通过将子查询的结果与原始表进行内连接,返回每个统一信用代码的最新记录。
具体代码实现:
SELECT t1.* FROM user t1 INNER JOIN ( SELECT idCard, MAX(time) AS max_time FROM user GROUP BY idCard ) t2 ON t1.idCard = t2.idCard AND t1.time = t2.max_time
表数据:
运行结果:
实现思路2(note:窗口函数从mysql8.0开始支持):
- 使用了窗口函数
ROW_NUMBER()
来为每个统一信用代码的记录分配一个行号。通过PARTITION BY
子句,我们按统一信用代码对数据进行分组,并使用ORDER BY
子句按更新时间降序排列。这样,每个统一信用代码的最新记录将被分配行号 1。然后,外部查询过滤出行号为 1 的记录,从而只返回每个统一信用代码的最新数据。
具体代码实现:
SELECT t.* FROM ( SELECT a.*, ROW_NUMBER() OVER (PARTITION BY a.idCard ORDER BY a.time DESC) AS rn FROM user a ) t WHERE t.rn = 1;