头哥sjjxpt，源代码
复杂查询
第1关 查询学生平均分
########## Begin ##########
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2)as avg_score from student b
inner join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union
select a.s_id,a.s_name,0 as avg_score from student a
where a.s_id not in (select distinct s_id from score);
########## End ##########
第2关 查询修课相同学生信息
########## Begin ##########
create view temp as(select s_id,group_concat(c_id)as c from score group by s_id);
select * from student where s_id in(select s_id from temp where c=(select c from temp where s_id="01")and s_id<>"01");
########## End ##########
第3关 查询各科成绩并排序
########## Begin ##########
select a.*,count(b.s_score)+1 rank from score a left join score b
on a.c_id = b.c_id and a.s_score <b.s_score
group by a.c_id,a.s_id
order by a.c_id,count(b.s_score);
########## End ##########
第4关 查询张老师课程成绩最高的学生信息
########## Begin ##########
select a.*,b.s_score,b.c_id,c.c_name from student a
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
where b.c_id = (select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name="张三")
and b.s_score in (select MAX(s_score)from score where c_id="02");
########## End ##########
第5关 查询两门课程不及格同学信息
########## Begin ##########
select a.s_id,a.s_name,ROUND(AVG(b.s_score))
avg_score from student a
inner join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(*)>=2
)
GROUP BY a.s_id,a.s_name;
########## End ##########
注意了·注意了·！！！拳套＋我WeChat（ 幺舞溜 漆舞贰吧 肆贰酒四）
要备注WeChat缘由窝！！！
当然是有偿分享的哟！（但绝对在君的可接受范围内!!!）
但是前三名直接免费分享哟，赶紧冲冲冲！！！
329
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
