mysql版本:5.7
sql如下:
SELECT b.student_id, b.create_time from (SELECT a.student_id, a.create_time FROM t_cardiopulmonary a ORDER BY a.create_time DESC ) b GROUP BY b.student_id
结果:
student_id为 1的学生 最新时间是2018-05-04 09:22:57
子查询sql结果集:
竟然是第三条数据,查询资料得知可以在子查询临时表中添加limit要包含查询出所有数据
修改sql如下:
SELECT b.student_id, b.create_time from (SELECT * FROM t_cardiopulmonary a ORDER BY a.create_time DESC LIMIT 999) b GROUP BY b.student_id
结果:
OK,成功解决问题,还可以用另一个写法:
select v.student_id, v.create_time from t_cardiopulmonary v
inner join (select student_id, max(create_time) 'create_time' from t_cardiopulmonary
group by student_id) c on v.student_id = c.student_id and v.create_time = c.create_time