SQL语句进阶练习（多表连接查询）-提供查询题目与解答

说明

第一眼看到表的结构可能心中会觉得题目比较简单，但是当我深入做题目的时候发现这题目可能有些变态，几乎费了我一天的时间才把这些题目做出来（可能是因为我不怎么聪明的原因）
在我所做的题解中有一部分不是最优解，还有很多高效的查询方式，比如最后一题的解答，我有三种方式解答，但是最后的一种才是最简单的行转列的方式。
在这些题目中使用最多的就是join多表连接查询

表的基础结构与数据

student表数据（学生表）

score表数据（成绩表）

course表数据（课程表）

teacher表数据（老师表）

查询题目

稍微基础题型

– 1.查询平均成绩大于60分的学生的学号和平均成绩
– 2.查询没有学全所有课的学生的学号、姓名
– 3.统计每门课程的学生选修人数(超过2人的课程才统计)要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
– 4.查询同名同姓学生名单并统计同名人数
– 5.查询两门以上不及格课程的同学的学号及其平均成绩
– 6.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

进阶题型

以下练习使用的数据库是schooldb
1.查询01课程成绩比02课程成绩高的学生的学号、姓名以及两门课分别的成绩
2.查询所有学生的学号、姓名、选课门数以及平均成绩
3.查询没有学过张三老师所教课程的学生的学号和姓名
4.查询选修了01课程并且也选修了02课程的学生的学号和姓名
5.查询所有选修的课程成绩都不及格的学生的学号和姓名
6.查询每门课程的编号、课程名称、平均分、最高分、最低分以及及格率
7.查询每个学生的学号、姓名以及每门课的成绩（每个学生对应查询结果中的一条记录，多门课程成绩显示在同一行）

题目解答

稍微基础题目解答

-- 1.查询平均成绩大于60分的学生的学号和平均成绩
-- 题目解析一 如果平均成绩为已选科目的平均分解答如下
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id HAVING `AVG(s_score)`>60
-- 題目解析二 如果平均分是总科目分数除以总门数，没有考当做0分解答如下
SELECT s_id,(SUM(s_score)/3) avg_score
FROM score
GROUP BY s_id HAVING `avg_score`>60

-- 2.查询没有学全所有课的学生的学号、姓名
SELECT st.s_id,st.s_name
FROM student st
LEFT JOIN
(
SELECT *
FROM score) sc
ON st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(sc.c_id)<3

-- 方法二
SELECT st.s_id,st.s_name
FROM
(
SELECT *,COUNT(c_id)
FROM score
GROUP BY s_id ) one
RIGHT JOIN student st
ON one.s_id=st.s_id
WHERE `COUNT(c_id)`<3 OR `COUNT(c_id)` IS NULL

-- 3.统计每门课程的学生选修人数(超过2人的课程才统计)
-- 要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT c_id,COUNT(s_id)
FROM score
GROUP BY c_id HAVING `COUNT(s_id)`>2
ORDER BY `COUNT(s_id)` DESC,c_id ASC

-- 4.查询同名同姓学生名单并统计同名人数
SELECT st.*,`COUNT(s_name)` number
FROM student st
JOIN
(
SELECT s_name,COUNT(s_name)
FROM student
GROUP BY s_name HAVING `COUNT(s_name)`>1) one
ON st.s_name=one.s_name

-- 5.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id,AVG(s_score)
FROM score
WHERE s_score<60
GROUP BY s_id HAVING COUNT(c_id)>=2

-- 6.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT st.s_id,s_name,`AVG(s_score)` avg_score
FROM student st
INNER JOIN 
(
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id HAVING `AVG(s_score)`>85) sc
ON st.s_id=sc.s_id

进阶题目解答

-- 1.查询01课程成绩比02课程成绩高的学生的学号、姓名以及两门课分别的成绩
SELECT st.s_id,s_name,c1.s_score 01score,c2.s_score 02score
FROM(
SELECT *
FROM score 
WHERE c_id='01') c1
JOIN(
SELECT *
FROM score 
WHERE c_id='02') c2
JOIN
student st
ON c1.s_id=c2.s_id AND st.s_id=c1.s_id AND c1.s_score>c2.s_score
-- 2.查询所有学生的学号、姓名、选课门数以及平均成绩
SELECT st.s_id,s_name,COUNT(st.s_id) c_number,AVG(s_score) 
FROM student st INNER JOIN score sc
ON st.s_id=sc.s_id 
GROUP BY s_name 
ORDER BY st.s_id ASC
-- 3.查询没有学过张三老师所教课程的学生的学号和姓名
SELECT s_id,s_name
FROM student
WHERE s_id NOT IN(
SELECT s_id
FROM course c INNER JOIN score sc INNER JOIN teacher t
ON t.t_id=c.t_id AND c.c_id=sc.c_id AND t_name='张三')
-- 4.查询选修了01课程并且也选修了02课程的学生的学号和姓名
SELECT message1.s_id,message1.s_name
FROM (
SELECT st.s_id,s_name
FROM student st,score sc
WHERE st.s_id=sc.s_id AND c_id='01') message1
INNER JOIN (
SELECT st.s_id,s_name
FROM student st,score sc
WHERE st.s_id=sc.s_id AND c_id='02') message2
ON message1.s_id=message2.s_id
-- 5.查询所有选修的课程成绩都不及格的学生的学号和姓名
SELECT st.s_id,s_name
FROM (
SELECT s_id,COUNT(s_id),COUNT(c_id)
FROM score
WHERE s_score<60
GROUP BY s_id) num
INNER JOIN
student st
ON st.s_id=num.s_id AND `COUNT(s_id)`=`COUNT(c_id)`
-- 6.查询每门课程的编号、课程名称、平均分、最高分、最低分以及及格率one.c_id,AVG(s_score) avg,MAX(s_score) max,MIN(s_score) min
SELECT one.c_id,`AVG(s_score)` avg,`MAX(s_score)` max,`MIN(s_score)` min,CONCAT((num/sum*100) + "%" ) pass
FROM (
SELECT c_id,AVG(s_score),MAX(s_score),MIN(s_score),COUNT(c_id) sum
FROM score
GROUP BY c_id) one
INNER JOIN (
SELECT c_id,COUNT(s_id) num
FROM score 
WHERE s_score>=60 
GROUP BY c_id) two
ON one.c_id=two.c_id
-- 7.查询每个学生的学号、姓名以及每门课的成绩（每个学生对应查询结果中的一条记录，多门课程成绩显示在同一行）
SELECT one.s_id '学号',one.s_name '姓名',one.s_score '语文',two.s_score '数学',three.s_score '英语'
FROM (
SELECT st.s_id,s_name,sc.c_id,s_score
FROM student st LEFT JOIN score sc
ON st.s_id = sc.s_id AND sc.c_id='01') one
JOIN (
SELECT st.s_id,s_name,sc.c_id,s_score
FROM student st LEFT JOIN score sc
ON st.s_id = sc.s_id AND sc.c_id='02') two
JOIN (
SELECT st.s_id,s_name,sc.c_id,s_score
FROM student st LEFT JOIN score sc
ON st.s_id = sc.s_id AND sc.c_id='03') three
ON one.s_id=two.s_id AND two.s_id=three.s_id
-- 查询参加所有考试的学生
SELECT one.s_id '学号',one.s_name '姓名',one.s_score '语文',two.s_score '数学',three.s_score '英语'
FROM (
SELECT st.s_id,st.s_name,c_name,s_score
FROM course c JOIN score sc JOIN student st
WHERE c.c_id=sc.c_id AND st.s_id=sc.s_id
ORDER BY st.s_id ASC) one
JOIN (
SELECT st.s_id,st.s_name,c_name,s_score
FROM course c,score sc,student st
WHERE c.c_id=sc.c_id AND st.s_id=sc.s_id
ORDER BY st.s_id ASC) two
JOIN (
SELECT st.s_id,st.s_name,c_name,s_score
FROM course c,score sc,student st
WHERE c.c_id=sc.c_id AND st.s_id=sc.s_id
ORDER BY st.s_id ASC) three
ON one.s_id=two.s_id AND two.s_id=three.s_id AND one.c_name != two.c_name AND two.c_name != three.c_name
AND (one.c_name='语文' OR one.c_name IS NULL) AND two.c_name='数学' AND three.c_name='英语' 
-- 行转列写法
SELECT st.s_id,st.s_name,IFNULL(`语文`,0),IFNULL(`数学`,0)`数学`,IFNULL(`英语`,0)`英语`
FROM student st LEFT JOIN
(SELECT s_id,MAX(IF(c_id='01',s_score,0)) '语文',MAX(IF(c_id='02',s_score,0)) '数学',MAX(IF(c_id='03',s_score,0)) '英语'
FROM score
GROUP BY s_id) sc
ON st.s_id=sc.s_id