最流行的查询需求分析07

演示数据准备的SQL

演示数据准备的SQL

需求演示

36、查询每一门课程成绩都在70分以上的姓名、课程名称和分数

group by + min() + in() 函数

用 group by 对学生id分组，用 min() 函数查出每门课程都大于70分的学生，然后用 in 匹配符合条件的学生

-- 36、查询每一门课程成绩都在70分以上的学生的姓名、课程名称和分数 SELECTst.s_name,co.c_name,sc.s_score 
FROMscore scLEFT JOIN course co ON co.c_id = sc.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
st.s_id in (-- 先查询出3个成绩都70分以上的学生的idselect s_id from score group by s_id having min(s_score) >= 70)

37、查询不及格的课程及学生

普通表连接查询

-- 37、查询不及格的课程及学生SELECTst.s_name,co.c_name,sc.s_score 
FROMscore scLEFT JOIN course co ON sc.c_id = co.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHEREsc.s_score < 60

38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名

普通表连接查询

-- 38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名SELECTst.s_id,st.s_name,co.c_name,sc.s_score 
FROMcourse coLEFT JOIN score sc ON sc.c_id = co.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHEREco.c_id = '01' AND sc.s_score >= 80

39、求每门课程的学生人数

长型数据格式（多行）和宽型数据格式（多列）

-- 39、求每门课程的学生人数-- 宽型数据格式SELECT sum(case when sc.c_id = '01' then 1 else 0 end) '语文',sum(case when sc.c_id = '02' then 1 else 0 end) '数学',sum(case when sc.c_id = '03' then 1 else 0 end) '英语'
FROMcourse coLEFT JOIN score sc ON co.c_id = sc.c_id-- 长型数据格式SELECTco.c_name, count(sc.s_id) '人数'
FROMcourse coLEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BYco.c_id

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩

子查询+limit

limit 1 返回查询结果的第一行数据

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩SELECTst.*,co.c_name,sc.s_score 
FROMstudent stLEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course co ON co.c_id = sc.c_id
WHEREst.s_id = (SELECTsc.s_idFROMteacher teLEFT JOIN course co ON co.t_id = te.t_idLEFT JOIN score sc ON sc.c_id = co.c_id WHEREte.t_name = '张三' ORDER BYsc.s_score DESC -- limit 1 返回查询结果的第一行数据LIMIT 1 )

41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

distinct 去重

-- 41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩SELECT distinct s1.*,co.c_name,st.s_name
FROMscore s1LEFT JOIN score s2 ON s1.c_id != s2.c_idLEFT JOIN course co ON co.c_id = s1.c_idLEFT JOIN student st ON st.s_id = s1.s_id  	
WHEREs1.s_score = s2.s_score

42、查询每门课程成绩最好的前两名

使用开窗函数 dense_rank() over()

使用这个开窗函数，查出排名后获取前两名就可以了，我这里连表是为了把数据展示的更完整清晰。

-- 42、查询每门课程成绩最好的前两名 SELECTst.s_id,st.s_name,t.c_name,t.s_score,t.drk 
FROMstudent st
RIGHT JOIN ( SELECT sc.*, co.c_name,dense_rank () over ( PARTITION BY sc.c_id ORDER BY sc.s_score DESC ) drk FROM score scLEFT JOIN course co on co.c_id = sc.c_id
) t ON t.s_id = st.s_id 
WHERE
t.drk IN (1,2)

子查询写法，不用开窗函数排序

和上面结果不一样是因为我这里只取前两名，没有考虑成绩并列相同的。
上面的开窗函数写法就有考虑成绩相同的并列排名

-- 子查询写法
SELECT* 
FROMscore s1 
WHERE-- 这个子查询相当于上面的开窗函数( SELECT count( s2.s_score ) FROM score s2 WHERE s1.c_id = s2.c_id AND s1.s_score < s2.s_score ) + 1 <= 2 ORDER BYs1.c_id,s1.s_score DESC

43、统计每门课程的学生选修人数(超过5人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

group by … having … order by

-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)，
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECTsc.c_id '课程编号',count( 1 ) cnt 
FROMscore sc 
GROUP BYsc.c_id 
HAVINGcount( 1 )>= 5 
ORDER BYcnt DESC,  -- 按人数降序排列sc.c_id ASC -- 按课程号升序排列

44、检索至少选修两门课程的学生学号

group by … having …

-- 44、检索至少选修两门课程的学生学号SELECTsc.s_id ,st.s_name,count( sc.s_id ) '选修课程数'
FROMscore scLEFT JOIN student st ON st.s_id = sc.s_id 
GROUP BYsc.s_id 
HAVINGcount( sc.s_id ) >= 2

45、查询选修了全部课程的学生信息

group by … having …

-- 45、查询选修了全部课程的学生信息SELECTst.* 
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BYsc.s_id 
HAVINGcount( sc.s_id ) = ( SELECT count( 1 ) FROM course )