闲来没事，谢谢sql玩

## 建表语句

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