一、题目

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

二、思路

第一步：计算学科排名和总成绩，可以使用rank和sum的开窗函数实现

第二步：计算总成绩排名，可以使用dense_rank实现

第三步：取每个学科排名第3的学生

三、解答

select 
  student,
  subject,
  score,
  subject_rank, -- 学科排名
  sum_score, -- 总成绩
  sum_score_rank
from (
  -- 第二步：计算总成绩排名，可以使用dense_rank实现
  select 
    student,
    subject,
    score,
    subject_rank, -- 学科排名
    sum_score, -- 总成绩
    dense_rank() over(order by sum_score) sum_score_rank
  from (
    -- 第一步：计算学科排名和总成绩，可以使用rank和sum的开窗函数实现
    select  
      student,
      subject,
      score,
      rank() over(partition by subject order by score desc) subject_rank, -- 学科排名
      sum(score) over(partition by student) sum_score -- 总成绩
    from xkcj_student_score 
    order by student,
      subject
  ) t11
) t21
where subject_rank = 3

结果：

数据

# 建表
--建表语句
create table if not exists xkcj_student_score(
    student string,
    subject string,
    score   bigint
) comment '查询每个学科第三名的学生的学科成绩总成绩及总排名'
stored as parquet
;
 
--插入数据
insert into xkcj_student_score(student, subject, score)
values ('张三', '语文', 95),
       ('李四', '语文', 90),
       ('王五', '语文', 88),
       ('赵六', '语文', 77),
       ('张三', '数学', 80),
       ('李四', '数学', 90),
       ('王五', '数学', 92),
       ('赵六', '数学', 84),
       ('张三', '英语', 82),
       ('李四', '英语', 93),
       ('王五', '英语', 88),
       ('赵六', '英语', 68);

注：题目获取自网络