## 建表语句
CREATE TABLE `sc` (
`S` varchar(10) DEFAULT NULL,
`C` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `course` (
`C` varchar(10) DEFAULT NULL,
`Came` varchar(10) DEFAULT NULL,
`T` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `teacher` (
`T` varchar(10) DEFAULT NULL,
`Tame` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`S` varchar(10) DEFAULT NULL,
`Sname` varchar(10) DEFAULT NULL,
`Sage` datetime DEFAULT NULL,
`Ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--36. 查询每门功成绩最好的前两名
-- 使用union all(select * from SC where c=01 ORDER BY score desc limit 2)
union all
(select * from SC where c=02 ORDER BY score desc limit 2)
union all
(select * from SC where c=03 ORDER BY score desc limit 2);
-- 自身左连接
select * from SC A
LEFT JOIN SC B -- (LEFT JOIN 避免头部数据关联不上)
on A.C = B.C and A.score < B.score
GROUP BY A.S, A.C
HAVING count(B.S) < 2 ;
# 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select * from (select * from SC where c = '01') A
left join (select * from SC where c = '02') B
on A.s = B.s
where A.score > B.score;
# 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sum(A.score), count(*), sum(A.score)/count(*) as pingjun from SC A
left join Student B
on A.S = B.S
group by B.S;
# 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select A.S, A.Sname,count(DISTINCT B.C) ,sum(B.score) from Student A
left join SC B
on A.S = B.S
GROUP BY A.s;
#6. 查询学过「张三」老师授课的同学的信息
select * from SC B
where B.C in( select A.C from Course A where A.T = '02');
select * from SC B
RIGHT JOIN (select A.C from Course A where A.T = '02') C
on B.C = C.C;
# 7. 查询没有学全所有课程的同学的信息 ( having 字句, having 是筛选组 而where是筛选记录,用having就一定要和group by连用。)
select * from (select A.S , count(*) as n from SC A group by A.S ) B
where B.n < 3;
# having
select A.S , count(*) as n from SC A group by A.S Having count(n) < 3
#8 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from SC A
where A.C in ( select C from SC B where B.S='01') and A.S <> '01'
group by A.S
#9 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from (select s,group_concat(c order by c) gc from SC group by s) a
join Student s on a.s=s.s where a.gc=(select group_concat(c) from SC where s=6)
#-11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select C.S,AVG(score) from SC C where C.S in (select A.s from SC A where A.score< 60
GROUP BY S
HAVING count(*) >2)
GROUP BY C.S
-- 13. (静态写法)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select S,
max(case C when '01' then score else 0 end) as 课程一,
max(case C when '02' then score else 0 end) as 课程二,
max(case C when '03' then score else 0 end) as 课程三, AVG(A.score) 平均分 from SC A
GROUP BY A.S ORDER BY 平均分 desc
-- 14. 查询各科成绩最高分、最低分和平均分:
-- 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
-- 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select A.C as 课程ID,B.Came 课程名称, count(*) 选修人数,
sum(case when score>=60 then 1 else 0 END)/count(*) as 及格率,
sum(case when score>=70 AND score< 80 then 1 else 0 END)/count(*) as 中等率,
sum(case when score>=80 AND score< 90 then 1 else 0 END)/count(*) as 优良率,
sum(case when score>=90 then 1 else 0 END)/count(*) as 优秀率
from SC A
JOIN Course B on A.C = B.C
group by A.C
ORDER BY count(*) desc, A.C desc;
-- 15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
-- 15.2 按各科成绩进行排序,并显示排名, Score 重复时合并名次
-- 参考:https://blog.csdn.net/a9925/article/details/76804951
select @rownum := @rownum + 1 无并列, score from SC ,(SELECT @rownum := 0) t
where c = 01
select @rownum,RANK() over(order by score desc)排名 from SC
--18. 查询各科成绩前三名的记录(方法 1, 使用分区)
-- 方法二;
select * from SC A
LEFT JOIN SC B on A.C = B.C and A.score < B.score
group by a.S,a.C,a.score ;
select * from SC A
LEFT JOIN SC B on A.C = B.C and A.score < B.score
where A.S = 02 and A.C= 01
order by A.score ,B.score desc
;
--20. 查询出只选修两门课程的学生学号和姓名
select s,Sname from student where s in ( select s from ((select s, count(C) 课程数 from SC GROUP BY S)) A where A.课程数=2 )
select B.s,Sname from student B
join (select s from ((select s, count(C) 课程数 from SC GROUP BY S)) A where A.课程数=2) C
on B.s = C.s
--25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
--31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select A.S, B.Sname, A.C , A.score from SC A
LEFT JOIN student B on A.S = B.S where A.C = 01 and A.score>=80
--32. 求每门课程的学生人数
select C,count(S) from SC GROUP BY C
--33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select * from course A
join teacher B
on A.T = B.T
join SC C
on A.C = C.C
join student D
on C.S = D.S
where B.Tame = '张三'
ORDER BY score desc
--34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
--37.统计每门课程的学生选修人数(超过5人的课程才统计)。
--要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select count(C) as n, A.C from sc A
GROUP BY A.C
HAVING count(C) > 5
ORDER BY n, A.C desc