tb_student表：
id name
30 d3
49 c3
51 a3
52 b3
tb_score表：
stu_id score
30 99
49 79
51 80
52 59
tb_class表：
id name
1 class-1
2 class-2
3 class-1
4 class-2
tb_student_class表
id stu_id class_id
1 30 1
2 49 2
3 51 1
4 52 2
输出
stu_id stu_name class_name score
51 a3 class-1 80
52 b3 class-2 59
select s.stu_id, stu.name as stu_name, cs.class_name, cs.score from
(select c.name as class_name, min(s.score) as score from tb_student_class sc left join tb_score s on sc.stu_id = s.stu_id left join tb_class c on sc.class_id = c.id group by c.name) cs
left join tb_score s on cs.score = s.score left join tb_student stu on s.stu_id = stu.id
用开窗函数,每个表都只用查一次
select t.stu_id, c.name, t.score, d.name class_name
from (select a.stu_id,
a.class_id,
b.score,
dence_rank() over(partition by a.class_id order by b.score) rk
from tb_student_class a, tb_score b
where a.stu_id = b.stu_id) t,
tb_student c,
tb_class d
where t.rk = 1
and t.stu_id = c.id
and t.class_id = d.id