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

前篇（1）和后篇（3）

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

若有帮助欢迎点赞、收藏、评论~

题解

12、查询和“01”号同学所学课程完全相同的其他同学的学号（！）

## 好吧，网上居然有人跟我一样用这种奇葩解法（个人觉得并不严谨——万一顺序不同所求字符串就不一样了）
SELECT m.s_id
FROM (
			SELECT s_id,
						 GROUP_CONCAT(c_id) c_str
			FROM Score
			GROUP BY s_id
			) m
WHERE c_str = (SELECT GROUP_CONCAT(c_id)
							FROM Score 
							WHERE s_id = '01');

13、查询没学过"张三"老师讲授的任一门课程的学生姓名（？）

## 这题在不断调试中做出来
WITH target_course_id AS   # 满足条件的所有课程id临时表
(
SELECT c_id 
FROM Course
WHERE t_id = (SELECT t_id FROM Teacher WHERE t_name = "张三")			 
),  
target_course_cnt AS   #  满足c_id条件的计数表
(
SELECT s_id,
			 IF(c_id IN (SELECT * FROM target_course_id), 1, 0) c_id_cnt
FROM Score 
)

SELECT st.s_name
FROM Student st
JOIN 
		(SELECT m.s_id
		 FROM (
					SELECT s_id, 
								 SUM(c_id_cnt) cnt 
					FROM target_course_cnt    #  计数临时表
					GROUP BY s_id
					) m # 满足条件课程的个数
		WHERE m.cnt = (SELECT COUNT(*) FROM target_course_id)  # 满足条件的课程id临时表
		) n  # 满足条件的 学生id
ON n.s_id = st.s_id;

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

SELECT st.s_id, st.s_name
FROM Student st
JOIN 
		(
		SELECT s_id,
					 SUM(IF(s_score < 60, 1, 0)) sum_score
		FROM Score 
		GROUP BY s_id
		HAVING sum_score >= 2
		) m 
ON st.s_id = m.s_id

16、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT st.s_id, st.s_name, st.s_birth, st.s_sex
FROM Student st
JOIN Score sc
WHERE sc.c_id = '01' AND sc.s_score < 60
ORDER BY sc.s_score DESC

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT sc.s_id, sc.s_score, m.avg_score
FROM Score sc
LEFT JOIN 
					(SELECT s_id,
								 AVG(s_score) avg_score
					FROM Score
					GROUP BY s_id
					) m 
ON sc.s_id = m.s_id
ORDER BY m.avg_score DESC

另解：

# 另解（会稍微有些区别）

SELECT m.s_id, m.s_score, m.avg_score
FROM 
		(
		SELECT s_id, s_score, 
					 AVG(s_score) OVER(PARTITION BY s_id) avg_score
		FROM Score 
		) m
ORDER BY m.avg_score DESC

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：

– 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– （及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90） （！！）

# 可以说，这是目前为止，写的最为复杂的一个查询（主要是合并了太多东西）——后来发现是乌龙
## 经验：不是最外层的字段，如果不想惹麻烦就一定不要使用汉字（内层表格中可以使用英文作为别名）
SELECT c.c_id "课程ID",
       c.c_name "课程名称",
			 p.max_score "最高分数",
			 p.min_score "最低分数",
			 p.avg_score "平均分数" ,
			 q.l4_rate "及格率",
			 q.l3_rate "中等率",
			 q.l2_rate "优良率",
			 q.l1_rate "优秀率"
FROM 
		(
		SELECT c_id, 
					 MAX(s_score) max_score, 
					 MIN(s_score) min_score, 
					 AVG(s_score) avg_score
		FROM Score
		GROUP BY c_id
		) p
JOIN 
		(
		SELECT m.c_id,
					 SUM(m.l4) / COUNT(*) l4_rate,
					 SUM(m.l3) / COUNT(*) l3_rate,
					 SUM(m.l2) / COUNT(*) l2_rate,
					 SUM(m.l1) / COUNT(*) l1_rate
		FROM 
				(
				SELECT c_id, 
							 IF(s_score >= 60 AND s_score < 70, 1, 0) l4,
							 IF(s_score BETWEEN 70 AND 80, 1, 0)  l3,
							 IF(s_score BETWEEN 80 AND 90, 1, 0) l2,
							 IF(s_score >= 90, 1, 0) l1
				FROM Score	 
				) m
		GROUP BY m.c_id 
		) q  # 各种率的计算
ON p.c_id = q.c_id  #  合并p和q两张计算的表
JOIN Course c 
ON p.c_id = c.c_id

另解：

SELECT sc.c_id "课程ID", c.c_name '课程名称',
			 MAX(sc.s_score) "最高分", 
			 MIN(sc.s_score) '最低分', 
			 AVG(sc.s_score) '平均分',
			 SUM(IF(sc.s_score BETWEEN 60 AND 70, 1, 0)) / COUNT(*) '及格率',
			 SUM(IF(sc.s_score BETWEEN 70 AND 80, 1, 0)) / COUNT(*) '中等率',
			 SUM(IF(sc.s_score BETWEEN 80 AND 90, 1, 0)) / COUNT(*) '优良率',
			 SUM(IF(sc.s_score >= 90, 1, 0)) / COUNT(*) '优秀率'
FROM Score sc 
JOIN Course c 
ON sc.c_id = c.c_id 
GROUP BY sc.c_id

19、按各科成绩进行排序，并显示排名

SELECT m.s_id, m.c_id, m.s_score, rk_score
FROM 
(
SELECT *,
			 ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) rk_score
FROM Score
) m
ORDER BY m.rk_score

20、查询学生的总成绩并进行排名

SELECT m.s_id, 
			 m.sum_score, 
			 ROW_NUMBER() OVER(ORDER BY m.sum_score DESC) score_rk
FROM 
		(
		SELECT s_id,
					 SUM(s_score) sum_score
		FROM Score
		GROUP BY s_id
		) m 
ORDER BY score_rk

21 、查询不同老师所教不同课程平均分从高到低显示

SELECT t.t_name, c.c_name, m.avg_score
FROM 
		(
		SELECT c_id, AVG(s_score) avg_score
		FROM Score
		GROUP BY c_id
		) m  # 不同老师所教不同课程平均分
JOIN Course c 
ON m.c_id = c.c_id
JOIN Teacher t 
ON c.t_id = t.t_id
ORDER BY m.avg_score DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT st.*, c.c_name, n.s_score
FROM 
		(
		SELECT * 
		FROM
				(
				SELECT *,
							 ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) score_rk
				FROM Score
				) m  # 排名
		WHERE m.score_rk = 2 OR m.score_rk = 3
		) n  # 取出第二三名
JOIN Student st 
ON st.s_id = n.s_id
JOIN Course c 
ON n.c_id = c.c_id

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

## 做复杂了：SUM(IF)是可以当做聚合字段与GZGROUP BY 直接使用的
SELECT n.*, c.c_name
FROM (
			SELECT m.c_id,
						 SUM(m.s_score) sum_score,
						 SUM(IF(m.score_level = 'l1', 1, 0)) l1_cnt,
						 SUM(IF(m.score_level = 'l2', 1, 0)) l2_cnt,
						 SUM(IF(m.score_level = 'l3', 1, 0)) l3_cnt,
						 SUM(IF(m.score_level = 'l4', 1, 0)) l4_cnt
			FROM 
					(
						SELECT s_id, c_id, s_score,
									CASE
												WHEN s_score BETWEEN 85 AND 100 THEN 'l1'
												WHEN s_score BETWEEN 70 AND 85 THEN 'l2'
												WHEN s_score BETWEEN 60 AND 70 THEN 'l3'
												ELSE 'l4'
									 END score_level
						 FROM Score
						 ) m
			GROUP BY m.c_id
			) n # 满足条件的课程分组 c_id 及其它信息
JOIN Course c 
ON n.c_id = c.c_id  # 获取课程名称c_name

24、查询学生平均成绩及其名次

SELECT m.*, ROW_NUMBER() OVER(ORDER BY m.avg_score DESC) rk_score  # 要理清层次和先后顺序
FROM 
(
SELECT s_id, AVG(s_score) avg_score
FROM Score
GROUP BY s_id
) m

25、查询各科成绩前三名的记录（不考虑成绩并列情况）

SELECT m.*
FROM
		(
		SELECT sc.c_id, c.c_name, sc.s_id, 
					 ROW_NUMBER() OVER(PARTITION BY sc.c_id ORDER BY sc.s_score DESC) rk_score
		FROM Score sc
		JOIN Course c 
		ON sc.c_id = c.c_id
		) m
WHERE m.rk_score <= 3

26、查询每门课程被选修的学生数

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

27、查询出只有两门课程的全部学生的学号和姓名

SELECT DISTINCT st.s_id, st.s_name   # 注意去重复（窗口函数不去重）
FROM 
		(SELECT m.*   # 注意子查询的层级关系
		 FROM
				(
				SELECT *, 
							 COUNT(c_id) OVER(PARTITION BY s_id) cnt_course
				FROM Score
				) m 
		WHERE m.cnt_course = 2
		) n
JOIN Student st
ON st.s_id = n.s_id

另解：直接使用聚合函数即可

SELECT st.s_id, st.s_name
FROM 
		(
		SELECT s_id, COUNT(c_id) cnt 
		FROM Score
		GROUP BY s_id
		HAVING cnt = 2
		) m 
JOIN Student st 
ON m.s_id = st.s_id 

28、查询男生、女生人数

## 使用条件语句（有点想复杂了）
SELECT SUM(IF(s_sex = "男",1 ,0)) cnt_male,
			 SUM(IF(s_sex = "女",1, 0)) cnt_female
FROM Student

# 另解：直接GROUP BY（如果不需要知道对应对象时）
SELECT COUNT(s_id) cnt
FROM Student 
GROUP BY s_sex

29、查询名字中含有"风"字的学生信息

SELECT *
FROM Student
WHERE s_name LIKE "%风%"

31、查询1990年出生的学生名单

SELECT s_name
FROM Student
WHERE s_birth LIKE '%1990%'

## 另解
SELECT s_name
FROM Student
WHERE YEAR(s_birth) = '1990'