mysql 请班级课程前3名的 分别什么课程多少分

经典SQL50题
#1、查询“01”课程比“02”课程成绩高的所有学生的学号（重点）；
 #解法一：
 –（构建一张（只有sid,c01,c02）字段的数据表）
 SELECT a.SId ,a.score ,b.score FROM
 (
 SELECT SId,score FROM sc WHERE CId=‘01’
 )as a
 INNER JOIN
 (
 SELECT SId ,score FROM sc WHERE CId=‘02’
 )as b ON a.SId=b.SId
 WHERE a.score>b.score;–解法二：
 –(把第一张表的课程为01的取出来；把第二张的表课程为02的取出来；然后第一张表的分>第二章的的分数)SELECT sc1.SId FROM sc as sc1
 INNER JOIN sc as sc2 ON sc1.SId=sc2.SId
 AND sc1.CId=‘01’ AND sc2.CId= ‘02’ and sc1.score>sc2.score;#2、查询平均成绩大于60分的同学的学号和平均成绩（简单）；
 –（尽量不要把group by 的字段以外字段写到select里面）SELECT SId ,avg(score) FROM sc GROUP BY SId HAVING avg(score)>60;
#3、查询所有同学的学号、姓名、选课数、总成绩（重点）
 –()
 SELECT total.SId,total.Sname,SUM(total.score),COUNT(*)
 FROM (
 SELECT student.SId,student.Sname,sc.score FROM student LEFT JOIN sc on student.SId=sc.SId
 )as total
 GROUP BY total.SId;#4、查询姓“李”的老师的个数；(简单)
 –（模糊查询（like）%是不定字长）SELECT * FROM teacher WHERE teacher.Tname LIKE ‘李%’;
#5、查询没学过“张三”老师课的同学的学号、姓名（重点）；
 –(把学过张三老师的同学找出来，然后就是没学过的了)SELECT student.SId,student.Sname FROM student
 WHERE student.SId NOT in
 (
 SELECT sc.SId FROM sc WHERE sc.CId=
 (
 SELECT course.CId FROM course WHERE course.TId=
 (
 SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’
 )))–解法二：
 –（构造学号、课程号、成绩、教师编号、教师姓名的一张表）SELECT student.Sname,student.SId FROM student WHERE student.SId not in (
 SELECT DISTINCT( sc.SId)
 FROM sc INNER JOIN course on sc.CId=course.CId
 INNER JOIN teacher ON course.TId=teacher.TId
 )#6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名（重点）；
 –(解法一：两个自连接)
 SELECT student.SId,student.Sname FROM student WHERE student.SId in (
 SELECT sc1.SId FROM sc as sc1 JOIN sc as sc2 ON sc1.SId=sc2.SId and sc1.CId=‘01’ and sc2.CId=‘02’)–（解法二：先找到选01的SID，再找到02的SID,再取交集）
SELECT student.Sname,student.SId FROM student WHERE student.SId IN(
 SELECT a.SId
 FROM(
 (SELECT sc.SId FROM sc WHERE sc.CId=‘01’)as a
 INNER JOIN
 (SELECT sc.SId FROM sc WHERE sc.CId=‘02’)as b
 on a.SId=b.SId) )#7、查询学过“张三”老师课的同学的学号、姓名（重点）；
SELECT student.Sname,student.SId FROM student WHERE student.SId in (
 SELECT sc.SId FROM sc WHERE sc.CId=(
 SELECT course.CId FROM course WHERE course.TId=(
 SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’)))#8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；（重点）（题1 题6 ）
 –（解法一）SELECT student.SId,student.Sname FROM student WHERE student.SId in (
 SELECT sc1.SId FROM sc sc1 INNER JOIN sc sc2 on sc1.SId=sc2.SId AND sc1.CId=‘01’ and sc2.CId=‘02’ and sc1.score>sc2.score);–（常规解法）
SELECT student.Sname,student.SId FROM student WHERE student.SId in (
 SELECT a.SId
 FROM(
 (SELECT sc.SId,sc.CId,sc.score FROM sc WHERE sc.CId=‘01’ )as a
 INNER JOIN
 (SELECT sc.SId, sc.CId,sc.score FROM sc WHERE sc.CId=‘02’) as b
 on a.SId= b.SId )WHERE a.score>b.score
 )
 #9、查询所有课程成绩小于60分的同学的学号、姓名(简单)；SELECT student.SId,student.Sname FROM student WHERE student.SId in(
 SELECT DISTINCT sc.SId FROM sc WHERE sc.score<60 );#10、查询没有学全所有课的同学的学号、姓名；
 –(注意有几个根本没有选课，他们就在sc表就就没有数据，这样很容易漏掉他们)
 SELECT student.Sname,student.SId FROM student WHERE student.SId NOT IN (
 SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(*)=(
 SELECT COUNT(1) FROM course ) )–（解法二，有学生表左连杰成绩表,这样没有选课的同学数据就不会没有）
SELECT stu.SId,stu.Sname FROM student AS stu WHERE stu.SId not IN(
 SELECT student.SId FROM student LEFT JOIN sc ON student.SId=sc.SId GROUP BY student.SId HAVING COUNT(1)=(
 SELECT COUNT(1) FROM course))#11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；(简单)
 SELECT student.SId,student.Sname FROM student WHERE student.SId IN(
 SELECT DISTINCT sc.SId FROM sc WHERE sc.CId in (SELECT sc.CId FROM sc WHERE sc.SId=‘01’))AND student.SId !=‘01’#12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名(重点)
 –（注意有一种情况：1号同学（1 2 3）
 2号同学（1 2 3）
 3号同学（1 2 4）
 4号同学（1 2 3 4） 
 此时这种情况时候只有2号同学与1号同学完全相同
 因此不能简单粗暴的让课程数=3，这样会把3号同学选进去 ）SELECT student.SId,student.Sname FROM student WHERE student.SId in (
 SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT()=(SELECT COUNT() FROM sc WHERE sc.SId=‘01’) )AND student.SId !=‘01’–（正确解法：01号学了01 02 03
 1、选出所学的课不在（01 02 03）的同学 -排除
 2、剩下的同学肯定选了01 02 03 中的某一门，判断所学的课程是否等于3）SELECT sc.SId FROM sc WHERE sc.SId!=‘01’ GROUP BY sc.SId HAVING count(1)=(
 SELECT count(1) FROM sc WHERE sc.SId=‘01’)
 AND
 sc.CId not IN(
 SELECT sc.SId FROM sc WHERE sc.CId not IN(
 SELECT sc.CId FROM sc WHERE sc.SId=‘01’))#13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
 SELECT avg(sc.score) FROM sc WHERE sc.CId =(
 SELECT course.CId FROM course WHERE course.TId =(
 SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’))#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT student.Sname FROM student WHERE student.SId NOT IN (
 SELECT sc.SId FROM sc WHERE sc.CId =(
 SELECT course.CId FROM course WHERE course.TId=(
 SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’) ))#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
 SELECT *
 FROM (
 (SELECT sc.SId,sc.score FROM sc WHERE sc.score<60 GROUP BY sc.SId HAVING COUNT(1)>=2) AS a
 INNER JOIN
 (SELECT student.Sname,student.SId FROM student)as b
 ON a.SId=b.SId)#16、检索"01"课程分数小于60，按分数降序排列的学生信息
 SELECT student.Sname,student.SId,sc.score FROM student INNER JOIN sc ON student.SId=sc.SId WHERE sc.score<60 AND sc.CId=‘01’ ORDER BY sc.score DESC#17、按平均成绩从高到低显示所有学生的平均成绩
SELECT a.*,RANK() over ( ORDER BY 平均成绩 DESC) as 排名 FROM
 (SELECT sc.SId,AVG(sc.score) 平均成绩 FROM sc GROUP BY sc.SId ORDER BY AVG(sc.score) DESC) as a#18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率(COUNT(if))（重点）
SELECT a.CId,a.Cname,b.*
 FROM (
 (SELECT course.CId,course.Cname FROM course )as a
 INNER JOIN
 (SELECT sc.CId, MAX(sc.score) 最高分,MIN(sc.score) 最低分,avg(sc.score) 平均分,count(IF(sc.score>=60,sc.score,NULL))/count(1) 及格率 FROM sc GROUP BY sc.CId) as b
 on a.CId=b.CId)#19、按各科平均成绩从低到高和及格率的百分数从高到低顺序(重点，count（if,）)
– count(IF（条件，条件为真结果，条件为假时结果）)
#case when then 表达式
 – case when THEN
 – CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE ‘不及格’ END
 – CASE SCORE WHEN ‘B’ THEN ‘良’ ELSE ‘不及格’ END
 – CASE SCORE WHEN ‘C’ THEN ‘中’ ELSE ‘不及格’ END-- CASE WHEN SCORE = 'A' THEN '优'– WHEN SCORE = ‘B’ THEN ‘良’
 – WHEN SCORE = ‘C’ THEN ‘中’ ELSE ‘不及格’ END--  ORDER BY 两个字段时（order by  字段1 DESC, 字段2 DESC）
 
 SELECT a.*,RANK() over (ORDER BY 平均成绩 DESC,及格率 DESC ) AS '排名' FROM 
 (SELECT sc.CId,AVG(sc.score) as 平均成绩,COUNT(IF(sc.score>=60,sc.score,NULL))/count(1) AS 及格率 
 FROM sc GROUP BY sc.CId ) AS a#20、查询学生的总成绩并进行排名
 SELECT a.*,RANK() over (ORDER BY sum DESC ) FROM
 (SELECT sc.SId,SUM(sc.score) as sum FROM sc GROUP BY sc.SId )as a#21、查询不同老师所教不同课程平均分从高到低显示（简单）
 SELECT *FROM
 ((SELECT course.CId,teacher.Tname FROM course LEFT JOIN teacher on course.TId=teacher.TId) as a
 INNER JOIN
 (SELECT sc.CId,AVG(sc.score) as 平均成绩 FROM sc GROUP BY sc.CId ORDER BY AVG(sc.score) DESC)as b
 ON a.CId=b.CId)#24、查询学生平均成绩及其名次(重点、窗口函数)
 – (不能用partition by 因为排序键是聚合函数)
 SELECT *,ROW_NUMBER()over(ORDER BY a.avg1 desc) as rank1
 FROM(SELECT SId,AVG(score) avg1 FROM sc GROUP BY SId)as a#26、查询每门课程名字被选修的学生数（简单）
 SELECT a.Cname,b.count
 FROM(
 (SELECT course.CId,course.Cname FROM course )as a
 INNER JOIN
 (SELECT sc.CId,COUNT(*)as count FROM sc GROUP BY sc.CId) as b
 on a.CId=b.CId)#27、查询出只选修了两门课程的全部学生的学号和姓名
 SELECT student.SId,student.Sname FROM student WHERE student.SId in(
 SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(*)=2);#28、查询男生、女生人数
 SELECT student.Ssex,count(1) FROM student GROUP BY student.Ssex;#29、查询名字中含有"风"字的学生信息
 SELECT * FROM student WHERE student.Sname like ‘%风%’;#30、查询同名同性学生名单，并统计同名人数
 SELECT student.Ssex,COUNT(*) FROM student GROUP BY student.Ssex#31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
 SELECT * FROM student WHERE YEAR(student.Sage)=‘1990’#32、查询每个课程之间的平均分并排序（从大到小排序）
 SELECT sc.CId,AVG(sc.score) FROM sc GROUP BY sc.CId ORDER BY AVG(sc.score) DESC#37、查询04号同学不及格的课程，并按课程号从大到小排列
 SELECT sc.CId FROM sc WHERE sc.SId=‘04’ AND sc.score<60 ORDER BY sc.CId DESC#38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
SELECT student.SId,student.Sname FROM student WHERE student.SId IN(
 SELECT sc.SId FROM sc WHERE sc.CId=‘01’ AND sc.score>=60)#40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT student.Sname FROM student WHERE student.SId=(
 SELECT sc.SId FROM sc WHERE sc.CId=(
 SELECT course.CId FROM course WHERE course.TId=(
 SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’)) ORDER BY sc.score DESC)#43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.CId,COUNT(1) FROM sc GROUP BY sc.CId HAVING COUNT(1)>=5
#44、检索至少选修两门课程的学生学号(简单)
 SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(1)>=2#45、查询选修了全部课程的学生信息
 SELECT * FROM student WHERE student.SId IN(
 SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(1)=(
 SELECT COUNT(1) FROM course))#46、查询各学生的年龄(重点)
 – YEAR(NOW())-YEAR(student.Sage) 是本题的重点
 – YEAR(date) 转换为年 NOW()表示当前时间
 SELECT student.Sname,YEAR(NOW())-YEAR(student.Sage) as 年龄 FROM student#47、查询本周过生日的学生
SELECT WEEK(NOW())#查看当前星期
 SELECT WEEK(sage) FROM Student#查看所有学生生日对应的星期
 SELECT sid,sname FROM Student WHERE WEEK(sage)=WEEK(NOW())#48、查询下周过生日的学生
SELECT sid,sname FROM Student WHERE WEEK(sage)=WEEK(NOW())+1
#49、查询本月过生日的学生
 SELECT sid,sname FROM Student WHERE MONTH(sage)=MONTH(NOW())#50、查询下月过生日的学生
 SELECT sid,sname FROM Student WHERE MONTH(sage)=MONTH(NOW())+1#51、查询课程编号为02的总成绩（简单）
 SELECT sc.CId,SUM(sc.score) FROM sc WHERE sc.CId=‘02’
 SELECT sum(sc.score)/COUNT(*) FROM sc WHERE sc.CId=‘02’#52查询所有成绩小于60分的学号与姓名（有陷阱！！！）
 –(注意有的同学只选了2门课程，因此就不能直接不及格科目数=3)
 –(思路：构建一个同学选了多少门课的表，构造一个同学不及格科目数的表
 两者取交集，这样就能求出所有课程不及格的学号与姓名)
 SELECT student.Sname,student.SId FROM student WHERE student.SId in(
 SELECT a.SId
 FROM(
 (SELECT sc.SId,COUNT() FROM sc GROUP BY sc.SId) as a
 INNER JOIN
 (SELECT sc.SId,COUNT() FROM sc WHERE sc.score<60 GROUP BY sc.SId) as b
 ON a.SId=b.SId))