SQL语句进阶练习（多表连接查询）-提供查询题⽬与解答
nginx是什么业务
说明
第⼀眼看到表的结构可能⼼中会觉得题⽬⽐较简单，但是当我深⼊做题⽬的时候发现这题⽬可能有些变态，⼏乎费了我⼀天的时间才把这些题⽬做出来（可能是因为我不怎么聪明的原因）
在我所做的题解中有⼀部分不是最优解，还有很多⾼效的查询⽅式，⽐如最后⼀题的解答，我有三种⽅式解答，但是最后的⼀种才是最简单的⾏转列的⽅式。
在这些题⽬中使⽤最多的就是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)`<3OR`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*
mysql面试题sql语句多表联查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
result怎么记忆GROUP BY s_id) num
cmd启动mysql无法启动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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。