# 练习-综合性练习一
现有三张表
- 学生信息表
- 课程信息表
- 学生成绩表
学生表 student
- 学号：student_no
- 姓名：student_name
- 性别：student_sex
- 籍贯：student_hometown
| student_no | student_name | student_sex | student_hometown |
|---|---|---|---|
| 1 | 张三 | 男 | 北京 |
| 2 | 李四 | 女 | 上海 |
| 3 | 王五 | 男 | 广州 |
| 4 | 赵六 | 女 | 深圳 |
| 5 | 孙七 | 男 | 成都 |
| 6 | 周八 | 女 | 重庆 |
课程表 course
- 课程号：course_no
- 课程名：course_name
- 总分：course_total_score
生成3门课程
| course_no | course_name | course_total_score |
|---|---|---|
| 1 | 数学 | 100 |
| 2 | 英语 | 100 |
| 3 | 语文 | 100 |
学生成绩表 score
- 学生成绩表id：id
- 学号：student_no
- 课程号：course_no
- 成绩：score
| id | student_no | course_no | score |
|---|---|---|---|
| 1 | 1 | 1 | 52 |
| 2 | 1 | 2 | 62 |
| 3 | 1 | 3 | 74 |
| 4 | 2 | 1 | 88 |
| 5 | 2 | 2 | 95 |
| 6 | 2 | 3 | 100 |
| 7 | 3 | 1 | 57 |
| 8 | 3 | 2 | 69 |
| 9 | 3 | 3 | 81 |
| 10 | 4 | 1 | 83 |
| 11 | 4 | 2 | 95 |
| 12 | 4 | 3 | 99 |
| 13 | 5 | 1 | 50 |
| 14 | 5 | 2 | 60 |
| 15 | 5 | 3 | 72 |
| 16 | 6 | 1 | 89 |
| 17 | 6 | 2 | 98 |
| 18 | 6 | 3 | 100 |
# 每个学生总成绩
查询每个学生的总成绩和学生信息
select
student.student_no student_no,
student_name,
student_sex,
student_hometown,
SUM(score) as total_score
from student
left join score on student.student_no = score.student_no
group by score.student_no
结果
| student_no | student_name | student_sex | student_hometown | total_score |
|---|---|---|---|---|
| 1 | 张三 | 男 | 北京 | 188 |
| 2 | 李四 | 女 | 上海 | 283 |
| 3 | 王五 | 男 | 广州 | 207 |
| 4 | 赵六 | 女 | 深圳 | 277 |
| 5 | 孙七 | 男 | 成都 | 182 |
| 6 | 周八 | 女 | 重庆 | 287 |
解析
- 使用left join 连接学生表和成绩表
- 使用group by 按照学号分组
- 使用SUM(score) 计算每个学生的总成绩
# 查询英语成绩排名
相同分数排名相同，下一名次+1
select
st.student_no,
st.student_name,
sc.score,
dense_rank() over (order by score desc) as rank
from score sc
join student st on sc.student_no = st.student_no
where sc.course_no = 2
结果
| student_no | student_name | student_sex | student_hometown | score | rank |
|---|---|---|---|---|---|
| 6 | 周八 | 女 | 重庆 | 98 | 1 |
| 2 | 李四 | 女 | 上海 | 95 | 2 |
| 4 | 赵六 | 女 | 深圳 | 95 | 2 |
| 3 | 王五 | 男 | 广州 | 69 | 3 |
| 1 | 张三 | 男 | 北京 | 62 | 4 |
| 5 | 孙七 | 男 | 成都 | 60 | 5 |
解析
- 使用
dense_rank()计算排名,dense_rank()窗口函数的作用是计算每个学生的排名，相同分数排名相同，下一名次+1 - 使用
where过滤课程号为2的英语成绩
# 查询每个课程的第一名
查询每个课程的第一名，存在并列的返回多个。并返回学生姓名(student_name)、课程名(course_name)、成绩(score)
正确答案
| student_name | course_name | score |
|---|---|---|
| 孙七 | 数学 | 89 |
| 周八 | 数学 | 89 |
| 周八 | 英语 | 98 |
| 李四 | 语文 | 100 |
| 周八 | 语文 | 100 |
# 方法一
SELECT student_name, course_name, score
FROM (
SELECT student_name, course_name, score,
dense_rank() OVER (PARTITION BY course_name ORDER BY score DESC) as rank
FROM (
select *
from student st
join score sc on sc.student_no = st.student_no
join course co on sc.course_no = co.course_no
)
) as ranked
WHERE rank = 1;
解析
- 使用
dense_rank()计算排名,dense_rank()窗口函数的作用是计算每个学生的排名，相同分数排名相同，下一名次+1 - 使用
where过滤排名为1的记录
# 方法二
SELECT
st.student_name,
co.course_name,
sc.score
FROM score sc
JOIN student st ON sc.student_no = st.student_no
JOIN course co ON sc.course_no = co.course_no
WHERE sc.score = (
SELECT MAX(score)
FROM score
WHERE course_no = sc.course_no
);
# 解析
该函数主要有三部分
- 主查询的
FROM和JOIN： - 子查询
WHERE条件的过滤
- 主查询的
FROM和JOIN：
SELECT
st.student_name,
co.course_name,
sc.score
FROM score sc
JOIN student st ON sc.student_no = st.student_no
JOIN course co ON sc.course_no = co.course_no
首先，score表与student表和course表进行连接，生成一个包含所有学生成绩、学生信息和课程信息的临时结果集。
- 子查询：
SELECT MAX(score)
FROM score
WHERE course_no = sc.course_no
- 对于主查询中的每一行，子查询都会执行一次，计算当前课程的最高分。
- 例如，如果主查询中当前行的
course_no是1，子查询会计算课程1的最高分。
WHERE条件的过滤：
WHERE sc.score = (
...
);
主查询中的每一行都会与子查询的结果进行比较，如sc.score等于子查询返回的最高分，则该行会被保留。
# 方法三
SELECT
st.student_name,
co.course_name,
sc.score
FROM score sc
JOIN student st ON sc.student_no = st.student_no
JOIN course co ON sc.course_no = co.course_no
JOIN (
SELECT
course_no,
MAX(score) AS max_score
FROM score
GROUP BY course_no
) AS max_scores ON sc.course_no = max_scores.course_no AND sc.score = max_scores.max_score;
# 解析
方法三和方法二的主要区别在于方法三使用了子查询，将计算最高分的逻辑单独抽取出来。将主查询的临时结果集与子查询的max_scores表进行连接，筛选出成绩等于课程最高分的学生记录。
# 查询每个课程的第二名
# 方法一
使用窗口函数dense_rank()计算排名
SELECT
student_name,
course_name,
score
FROM (
SELECT
st.student_name,
co.course_name,
sc.score,
dense_rank() OVER (PARTITION BY sc.course_no ORDER BY sc.score DESC) AS rank
FROM score sc
JOIN student st ON sc.student_no = st.student_no
JOIN course co ON sc.course_no = co.course_no
) AS ranked
WHERE rank = 2;
# 方法二
SELECT
st.student_name,
co.course_name,
sc.score
FROM score sc
JOIN student st ON sc.student_no = st.student_no
JOIN course co ON sc.course_no = co.course_no
WHERE sc.score = (
SELECT MIN(score)
FROM (
SELECT DISTINCT score
FROM score
WHERE course_no = sc.course_no
ORDER BY score DESC
LIMIT 2
) AS top_two
);
# 解析
- 主查询的FROM和JOIN：
score表与student表和course表进行连接，生成一个包含所有学生成绩、学生信息和课程信息的临时结果集。
- 子查询的执行：
- 对于主查询中的每一行，子查询都会执行一次，计算当前课程的第二名成绩。
- 内层子查询获取当前课程的前2个不同成绩，外层子查询取其中的最小值（即第二名成绩）。
内层子查询先在相同课程下，进行DESC降序排序，然后使用LIMIT 2取前2个成绩，外层子查询取其中的最小值（即第二名成绩）。
- WHERE条件的过滤：
- 主查询中的每一行都会与子查询的结果进行比较，如果
sc.score等于子查询返回的第二名成绩，则该行会被保留。
# 查询各科目第一名次数最多的学生
# 方法一
select student_name, max(time) time
from (
SELECT student_name, count(student_no) as time
FROM (
SELECT student_name, course_name, student_no,
dense_rank() OVER (PARTITION BY course_name ORDER BY score DESC) as rank
FROM (
select *
from student st
join score sc on sc.student_no = st.student_no
join course co on sc.course_no = co.course_no
)
) as ranked
WHERE rank = 1
group by student_no
)
结果
| student_name | time |
|---|---|
| 周八 | 3 |
# 解析
- 通过子查询，我们先筛选出一名的学生，并使用
group by进行分组算出 出现第一名学生(同一人)的次数count - 然后使用
max(time)获取出现次数最多的学生
# 查询每个课程中成绩大于李四的学生
select
st.student_name,
co.course_name,
sc.score
from student st
join score sc on sc.student_no = st.student_no
join course co on sc.course_no = co.course_no
where sc.score > (
select score
from score
where student_no = 2 and course_no = sc.course_no
)
结果
| student_name | course_name | score |
|---|---|---|
| 孙七 | 数学 | 89 |
| 周八 | 数学 | 89 |
| 周八 | 英语 | 98 |
# 解析
- 通过子查询，我们先筛选出李四的成绩，然后使用
where过滤出大于李四的成绩 - 然后使用
join连接学生表和成绩表，并使用join连接课程表和成绩表 - 最后使用
where过滤出大于李四的成绩