SQL 经典50题（题目+解答）（3）

前篇（1）和（2）

• SQL 经典50题（题目+解答）（1）
• SQL 经典50题（题目+解答）（2）

题解

32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

## 窗口函数
SELECT DISTINCT st.s_id, st.s_name, n.avg_score   ## 使用窗口函数必须使用DISTINCT
FROM
		(SELECT *
			FROM
					(
					SELECT s_id, s_score, 
								 AVG(s_score) OVER(PARTITION BY s_id) avg_score
					FROM Score 
					) m 
			WHERE m.avg_score > 85
		) n
JOIN Student st
ON n.s_id = st.s_id

# 聚合函数
SELECT st.s_id, st.s_name, AVG(sc.s_score) avg_score
FROM Student st 
JOIN Score sc 
ON st.s_id = sc.s_id
GROUP BY st.s_id
HAVING avg_score >= 85;

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT c_id, 
			 AVG(s_score) avg_score
FROM Score
GROUP BY c_id
ORDER BY avg_score ASC, c_id DESC;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT st.s_name, sc.s_score
FROM Score sc
JOIN Course c 
ON sc.c_id = c.c_id 
		AND c.c_name = "数学"  # 因为是INNER JOIN，可以不放在WHERE
		AND sc.s_score < 60
JOIN Student st
ON sc.s_id = st.s_id

35、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT st.s_id, st.s_name, sc.c_id, c.c_name, sc.s_score
FROM Student st
LEFT JOIN Score sc 
ON sc.s_id = st.s_id
LEFT JOIN Course c
ON c.c_id = sc.c_id 

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

## 错误：没有考虑没有选课和没有分数的情况
-- SELECT st.s_name, c.c_name, n.s_score
-- FROM 
-- 		(
-- 				SELECT * 
-- 				FROM 
-- 						(
-- 						SELECT *, 
-- 									 MIN(s_score) OVER(PARTITION BY s_id ORDER BY s_score) min_score
-- 						FROM Score
-- 
-- 						) m  # [所有]这个条件的方法之一：通过找边界值来进行限定
-- 				WHERE m.min_score > 70
-- 		) n
-- JOIN Student st 
-- ON st.s_id = n.s_id
-- JOIN Course c 
-- ON c.c_id = n.c_id
-- 

## 正解
SELECT n.s_name, n.c_name, n.score  
FROM 
		( 
		SELECT s_id,
	         MIN(s_score) min_score
    FROM Score sc
		GROUP BY s_id
		HAVING min_score > 70
		) m  # 满足条件的学生id
JOIN 
		(
		SELECT st.s_id, st.s_name, IFNULL(sc.s_score, 0) score, c.c_name
		FROM Student st 
		LEFT JOIN Score sc 
		ON st.s_id = sc.s_id
		LEFT JOIN Course c
		ON c.c_id = sc.c_id
		) n  # 将特殊值变为0
ON m.s_id = n.s_id

37、查询学生不及格的课程并按课程号从大到小排列

SELECT st.s_id,st.s_name, c.c_id, c.c_name
FROM Score sc 
JOIN Course c
ON sc.c_id = c.c_id
JOIN Student st
ON st.s_id = sc.s_id
WHERE sc.s_score < 60
ORDER BY c_id DESC

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

SELECT st.s_id, st.s_name
FROM Score sc
JOIN Student st
ON sc.s_id = st.s_id
WHERE sc.c_id = '03' AND sc.s_score > 80

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

SELECT c.c_id, c.c_name, COUNT(sc.s_id) cnt_student
FROM Score sc
JOIN Course c 
ON sc.c_id = c.c_id
GROUP BY c.c_id

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT m.s_name, m.c_name, m.s_score
FROM 
		(			
		SELECT st.s_name, c.c_name, sc.s_score,
					 RANK() OVER(PARTITION BY c.c_id ORDER BY sc.s_score) rk_score
		FROM Score sc 
			JOIN Course c 
			ON sc.c_id = c.c_id
			JOIN Teacher t
			ON t.t_id = c.t_id
			JOIN Student st
			ON st.s_id = sc.s_id
		WHERE t.t_name = "张三"
		) m
WHERE m.rk_score = 1

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

SELECT sc1.s_id, sc1.c_id, sc1.s_score  # 全部的字段来自一张表：使用自相连
FROM Score sc1
JOIN Score sc2 
ON sc1.s_score = sc2.s_score 
	 AND sc1.s_id != sc2.s_id 
	 AND sc1.c_id != sc2.c_id

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

SELECT st.s_id, st.s_name, c.c_id, c.c_name, n.s_score
FROM
(
		SELECT * 
		FROM 
				(
				SELECT *,
							 ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) rk_score
			  FROM Score 
				) m 
		WHERE m.rk_score <= 2
) n 
JOIN Student st
ON n.s_id = st.s_id 
JOIN Course c
ON c.c_id = n.c_id

43、统计每门课程的学生选修人数（超过5人的课程才统计）。

– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT c_id, COUNT(s_id) cnt_student
FROM Score
GROUP BY c_id
HAVING cnt_student > 5
ORDER BY cnt_student DESC, c_id ASC

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

SELECT m.s_id  # 用窗口函数更简单
FROM 
		(
		SELECT s_id, 
					 COUNT(c_id) cnt_course
		FROM Score
		GROUP BY s_id
		HAVING cnt_course >= 2
		) m

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

## 【全部】这个条件使用”计数“的方式来进行限制
SELECT DISTINCT st.*
FROM 
		(
		SELECT * 
		FROM
				(
				SELECT *,
							 COUNT(*) OVER(PARTITION BY s_id) cnt_course
				FROM Score
				) m  # 课程数量
		WHERE cnt_course = (SELECT COUNT(c_id) FROM Course)
		) n  # 满足课程数量
JOIN Student st
ON n.s_id = st.s_id;

46、查询各学生的年龄（精确到月份）

SELECT IF(m.month != 0, CONCAT(m.year, "岁", m.month, "个月"), CONCAT(m.year, "岁"))  "年龄"
FROM 
		(
		SELECT FLOOR(DATEDIFF(CURRENT_DATE, s_birth) / 365) year, 
					 ROUND((DATEDIFF(CURRENT_DATE, s_birth) % 365) / 30) month
		FROM Student
		) m
		

47、查询没学过“张三”老师讲授的任一门课程的学生姓名

# 有点小难
SELECT n.s_name
FROM 
		(
		SELECT st.s_name, COUNT(c_id)
		FROM Student st 
		LEFT JOIN 
				(
				SELECT sc.*, c_name, t_name
				FROM Score sc 
				JOIN Course c
				ON c.c_id = sc.c_id
				JOIN Teacher t
				ON t.t_id = c.c_id AND t.t_name = "张三"
				) m   # 满足张三老师课程的全部信息
		ON st.s_id = m.s_id
		GROUP BY st.s_id
		HAVING cnt_course = 0  # 与学生信息LEFT JOIN，找出交集数量为0的学生
		) n

48、查询两门以上不及格课程的同学的学号及其平均成绩

SELECT n.s_id, AVG(sc.s_score) avg_score
FROM
		(
		SELECT m.s_id
		FROM (
					SELECT *,
								 ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score) rk_score
					FROM Score
					) m
		WHERE rk_score = 2 AND s_score < 60  # 倒数第二门成绩的分数小于60分
		) n  # 满足条件的学生id
JOIN Score sc 
ON n.s_id = sc.s_id 
GROUP BY sc.s_id

49、查询本月过生日的学生

SELECT s_name
FROM Student
WHERE MONTH(s_birth) = MONTH(CURRENT_DATE)

49.1、查询下月过生日的学生

SELECT s_name
FROM Student
WHERE MONTH(s_birth) = MONTH(CURRENT_DATE) + 1

50、查询本周过生日的学生

SELECT s_name
FROM Student
WHERE WEEK(s_birth) = WEEK(CURRENT_DATE)

50.1、查询下周过生日的学生

SELECT s_name
FROM Student
WHERE WEEK(s_birth) = WEEK(CURRENT_DATE) + 1