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