mysql查询各科成绩前两名_mysql 查询：同名同姓、成绩相同、成绩最好的前两名...

ER图

4a5ba7cd036a0f948f72d2f2fde4a036.png

student表

ab1f5f079ca0c00c7784516ad88f7872.png

score 表

901c9d067f47fdf84efce4e39b59daf3.png

1-- 查询学过"张三"老师授课的同学的信息

select s.* ,t_name from student s left join score sc on s.s_id = sc.s_id
left join course c on sc.c_id = c.c_id
left join teacher t on c.t_id = t.t_id
where t_name = '张三';

2-- 查询同名同性学生名单，并统计同名人数

select s_name,s_sex,count(s_name) 
from student
group by s_name,s_sex
having count(s_name)>1;

3-- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

select sc1.*
from score sc1 ,score sc2 
where sc1.s_id=sc2.s_id and sc1.score=sc2.score and sc1.c_id<>sc2.c_id
group by sc1.s_id, sc1.c_id;

4 -- 查询每门课程成绩最好的前两名

select * from score sc1 where (select count(*) from score sc2 where sc1.c_id = sc2.c_id and
sc1.score <= sc2.score) <3
order by sc1.c_id , sc1.score desc;