昨天一大清早,睡眼惺忪着就跑去陆家嘴某金融数据公司面一ETL 岗。多年的数据仓库经验,自认为面试ETL岗应该问题不大,熟知却在一道简单的SQL题上被卡住了。
题目大概是这样,有一张学生成绩表S, 含有成绩,学生好,科目, 和分数(grade, student_id, subject, score) , 现在要求查出每个年级总成绩排名在第10到第20 的学生ID。
这样的问题,大家再熟悉不过了,心中也立马有了答案:
SELECT
grade ,
student_id
FROM
(
SELECT
grade ,
student_id row_number() over (partition BY grade ORDER BY total_score DESC) AS rank
FROM
(
SELECT
grade ,
student_id ,
SUM(score) AS total_score
FROM
S
GROUP BY
grade ,
student_id ) AS total ) AS t
WHERE
t.rank BETWEEN 10 AND 20
简单解释了下SQL 逻辑, 这时那面试的指出&#x