题目:
查询出每个学生的每门科目成绩,如果存在多条就取update_time最新的那条
CREATE TABLE `tb_score` (
`id` int(11) NOT NULL,
`user_id` varchar(20) NOT NULL,
`user_name` varchar(50) NOT NULL,
`menu_name` varchar(50) NOT NULL,
`score` double(5,2) NOT NULL,
`update_time` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
解答
select a.*,b.score from (
select user_id,user_name,menu_name,max(update_time) time
from tb_score a group by user_id,user_name,menu_name
) a
left join tb_score b on a.user_id=b.user_id and a.menu_name=b.menu_name and a.time=b.update_time
order by a.user_id