文章详情页_大数据SQL-查询每个学科第三名的学生的学科成绩总成绩

有学生成绩表，包含学生姓名、学科、成绩三个字段，请用一条 SQL 查询出每个学科排名第三名的学生，他的学科成绩、总成绩、以及总排名。

样例数据

+------------+------------+--------+
| student    | subject    | score  |
+------------+------------+--------+
| 张三       | 语文       | 95     |
| 李四       | 语文       | 90     |
| 王五       | 语文       | 88     |
| 赵六       | 语文       | 77     |
| 张三       | 数学       | 80     |
| 李四       | 数学       | 90     |
| 王五       | 数学       | 92     |
| 赵六       | 数学       | 84     |
| 张三       | 英语       | 82     |
| 李四       | 英语       | 93     |
| 王五       | 英语       | 88     |
| 赵六       | 英语       | 68     |
+------------+------------+--------+

期望结果

+------------+----------+--------+--------------+-----------+
| student    | subject  | score  | total_score  | total_rn  |
+------------+----------+--------+--------------+-----------+
| 王五       | 语文     | 88     | 268          | 2         |
| 张三       | 英语     | 82     | 257          | 3         |
| 赵六       | 数学     | 84     | 229          | 4         |
+------------+----------+--------+--------------+-----------+

分析

本题要求查询排名第三的学生的成绩，考察的是排序函数，要查询总成绩，考察 sum() over(partition by ) 开窗函数。总排名则是再一次的考察排序函数。

SQL

根据学科排名，并统计出每个学生的总成绩

查看学科内排名，使用 row_number() 函数，按照学科进行分组，分数倒序排名。也可以使用 rank()、dense_rank()，题目并无特殊要求，这里使用 row_number()，如果有特殊要求可以根据不同要求使用，并无影响。

执行 SQL

select 
    student
    ,subject
    ,score
    ,row_number() over(partition by subject order by score desc)    as subject_rn
    ,sum(score) over(partition by student)   as total_score
from student_score;

返回结果

+----------+----------+--------+-------------+--------------+
| student  | subject  | score  | subject_rn  | total_score  |
+----------+----------+--------+-------------+--------------+
| 张三     | 数学     | 80     | 4           | 257          |
| 张三     | 英语     | 82     | 3           | 257          |
| 张三     | 语文     | 95     | 1           | 257          |
| 李四     | 数学     | 90     | 2           | 273          |
| 李四     | 英语     | 93     | 1           | 273          |
| 李四     | 语文     | 90     | 2           | 273          |
| 王五     | 数学     | 92     | 1           | 268          |
| 王五     | 英语     | 88     | 2           | 268          |
| 王五     | 语文     | 88     | 3           | 268          |
| 赵六     | 数学     | 84     | 3           | 229          |
| 赵六     | 英语     | 68     | 4           | 229          |
| 赵六     | 语文     | 77     | 4           | 229          |
+----------+----------+--------+-------------+--------------+

根据学生总分计算学生总排名

先计算学生的总排名，已经在每行（即每个学科的记录）上添加了学生的总分，所以我们使用 dense_rank() 函数，按照总分排序。这里就可以得出一个总排名，可以在结果中看到，同一个学生每个学科记录上的总分排名是一致的。

执行 SQL

select 
    student
    ,subject
    ,score
    ,subject_rn 
    ,total_score
    ,dense_rank() over(order by total_score desc) total_rn
from (
    select 
        student
        ,subject
        ,score
        ,row_number() over(partition by subject order by score desc)    as subject_rn
        ,sum(score) over(partition by student)   as total_score
    from student_score
);

返回结果

+----------+----------+--------+-------------+--------------+-----------+
| student  | subject  | score  | subject_rn  | total_score  | total_rn  |
+----------+----------+--------+-------------+--------------+-----------+
| 李四     | 数学     | 90     | 2           | 273          | 1         |
| 李四     | 英语     | 93     | 1           | 273          | 1         |
| 李四     | 语文     | 90     | 2           | 273          | 1         |
| 王五     | 数学     | 92     | 1           | 268          | 2         |
| 王五     | 英语     | 88     | 2           | 268          | 2         |
| 王五     | 语文     | 88     | 3           | 268          | 2         |
| 张三     | 数学     | 80     | 4           | 257          | 3         |
| 张三     | 英语     | 82     | 3           | 257          | 3         |
| 张三     | 语文     | 95     | 1           | 257          | 3         |
| 赵六     | 数学     | 84     | 3           | 229          | 4         |
| 赵六     | 英语     | 68     | 4           | 229          | 4         |
| 赵六     | 语文     | 77     | 4           | 229          | 4         |
+----------+----------+--------+-------------+--------------+-----------+

查询每个学科的第三名

我们已经把所有需要的字段都查询出来了，只需要限定 subject_rn = 3 得到学科排名第三的同学记录即可。

执行 SQL

 select 
    student
    ,subject
    ,score
    ,total_score
    ,total_rn
from (
    select 
        student
        ,subject
        ,score
        ,subject_rn 
        ,total_score
        ,dense_rank() over(order by total_score desc) total_rn
    from (
        select 
            student
            ,subject
            ,score
            ,row_number() over(partition by subject order by score desc)    as subject_rn
            ,sum(score) over(partition by student)   as total_score
        from student_score
    )
)
where subject_rn = 3;

返回结果

+------------+----------+--------+--------------+-----------+
| student    | subject  | score  | total_score  | total_rn  |
+------------+----------+--------+--------------+-----------+
| 王五       | 语文     | 88     | 268          | 2         |
| 张三       | 英语     | 82     | 257          | 3         |
| 赵六       | 数学     | 84     | 229          | 4         |
+------------+----------+--------+--------------+-----------+

建表语句

-- drop table student_score;
create table if not exists student_score (
    student string,
    subject string,
    score   bigint
)
comment '学生成绩表';

示例数据

-- 样例数据
insert into student_score(student, subject, score) values 
    ('张三', '语文', 95),
    ('李四', '语文', 90),
    ('王五', '语文', 88),
    ('赵六', '语文', 77),
    ('张三', '数学', 80),
    ('李四', '数学', 90),
    ('王五', '数学', 92),
    ('赵六', '数学', 84),
    ('张三', '英语', 82),
    ('李四', '英语', 93),
    ('王五', '英语', 88),
    ('赵六', '英语', 68);

参考资料

常见大数据面试SQL-查询每个学科第三名的学生的学科成绩总成绩及总排名

原创文章，转载请注明出处：http://www.opcoder.cn/article/78/