一道简单的SQL面试题:查询成绩排名第10到第20的学生
来源:互联网 发布:淘宝上二手电脑使用 编辑:程序博客网 时间:2024/05/11 13:03
昨天一大清早,睡眼惺忪着就跑去陆家嘴某金融数据公司面一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 逻辑, 这时那面试的指出, 这是基于分析函数做的, 那么只通过标准的SQL 99, 有没有办法实现呢。也就是说没有什么 limit, first, top, row_num 这些不同数据库提供的函数情况下,怎么实现这个问题。还是这么个简单的问题,但却脑袋顿时僵住了,太习惯了用那些高级函数处理问题了,顷刻间却变得不知所措。
我首先想到的是如何取第2名的方法: 先找到每个年级第一名的, 再去找每个年级分数小于第一名的那个最大分数所对应的学生
以此类推,
第三名就是总分小于第二名的分数的最大分数
第四名就是总分小于第三名的分数的最大分数
。。。
这样子至少能得到结果, 但自己也觉得这样的SQL写出来有点太粗暴了。
面试官对我笑笑,让我回去想想, 这个简单的问题至少有5种做法。 有5种, 而我一种都不中。无地自容,自惭形愧。
回来的路上还在纠结这个问题,总感觉这个SQL 应该很简单, 但怎么就想不出呢。网上也搜了搜, 也都是些 用 top, limit 之类的方法, 没找到任何一个用普通SQL 实现的语句。真是年纪大了, 脑袋不好使,坐车的时候在想, 走路的时候在想,直到吃饭的时候也勉强想出了一招:
select
master.grade,
master.student_id,
count(1) + 1
from
(select
grade
, student_id
, sum(score) as total_score
from S group by grade, student_id) as master
left join
(select
grade
, student_id
, sum(score) as total_score
from S group by grade, student_id) as cmp
on master.grade = cmp.grade
and master.total_score < cmp.total_score
group by
master.grade,
master.student_id
having count(1) between 9 and 19
用表的自关联,让每个学生与比自己成绩高的学生连接到一起, 再分组count比自己分数高的同学的个数, 所以第10 名到第20名, 自然就是比自己分数高的个数在9 到19 之间的。谢天谢地想出来一个法子了,多少能让我吃下饭了。也许这query 还有漏洞,但这是一个思路。
可惜还有4种方法没找到答案? thinking。。。也望江湖上的能有大侠指点迷津。。。
- 一道简单的SQL面试题:查询成绩排名第10到第20的学生
- 一道简单的面试题
- 一道简单的面试题
- 一道简单的面试题
- 一道SQL表关联查询的面试题
- 一道比较简单的Google面试题
- 一道简单的面试题(数组)
- 一道简单的支付宝面试题
- 一道简单的阶层面试题
- 一道简单的java面试题
- 一道有趣的面试题 简单DP?
- 一道简单PHP数组的面试题
- 一道看似简单的面试题
- 一道简单而又不简单的面试题
- 一道sql面试题的解答
- 一道SQL的面试题之联想
- 一道SQL的面试题之联想
- 一道关于sql的面试题
- Android_ActionBar
- 2014:新星商城
- Android textiview 截长为...
- Waterfox 30.0 发布 - Firefox的64位优化版
- 框架、架构和设计模式之区分
- 一道简单的SQL面试题:查询成绩排名第10到第20的学生
- 【iOS开发】 UITableView SDK所有方法总结
- Android_JSON解析
- java LockSupport
- 一个利用html5的图片裁剪功能(已解决ios压扁缩放等bug)
- 计算机编码问题
- Linux常用工具Putty ssh连接
- Jsp指令---page(页面)指令
- android Activity切换动画效果