这是 HackerRank 上的一道中级难度的 SQL 挑战题,实际上考察的是动态排序。
有两个表:Students 和 Grades,Students 记录了学生的分数,Grades 存储了分数和绩点的对应关系。Students 包含了三个字段:ID、Name(姓名)、Marks(分数)。
Column | Type |
---|---|
ID | Integer |
Name | String |
Marks | Integer |
Grades 有三个字段:Grade(绩点)、Min_Mark(最小分值)、Max_Mark(最大分值)。它的数据如下:
Grade | Min_Mark | Max_Mark |
---|---|---|
1 | 0 | 9 |
2 | 10 | 19 |
3 | 20 | 29 |
4 | 30 | 39 |
5 | 40 | 49 |
6 | 50 | 59 |
7 | 60 | 69 |
8 | 70 | 79 |
9 | 80 | 89 |
10 | 90 | 100 |
根据这两张表,生成一份学习成绩单,这份成绩单要包含这三个字段:Name、Grade、Mark 。成绩单需要满足以下几个要求:
NULL
代替。输入样例
ID | Name | Marks |
---|---|---|
1 | Julia | 88 |
2 | Samantha | 68 |
3 | Maria | 99 |
4 | Scarlet | 78 |
5 | Ashley | 63 |
6 | Jane | 81 |
输出结果
Maria 10 99
Jane 9 81
Julia 9 88
Scarlet 8 78
NULL 7 63
NULL 7 68
先把 Students 的 Marks 翻译成 Grade,这个通过 Students 和 Grades 关联查询就能做到。
SELECT
name,
grade,
marks
FROM
Students
INNER JOIN Grades
ON marks BETWEEN min_mark
AND max_mark
接着就是排序和格式化的操作。排序涉及到三个字段,首先依据 grade 做降序排序;其次,根据 grade 的范围选择 name 或者 marks 做升序排序。因此,ORDER
子句可以这么写:ORDER BY grade DESC,IF(grade >= 8, name, marks)
。
完整的 SQL (MySQL)实现:
SELECT
IF(grade >= 8, name, NULL) AS name,
grade,
marks
FROM
Students
INNER JOIN Grades
ON marks BETWEEN min_mark
AND max_mark
ORDER BY grade DESC,
IF(grade >= 8, name, marks)
留一个思考题,如果把排序的条件“如果绩点 < 8 ,就再按照分数升序排序”中的“升序排序”改成“降序排序”,你会怎么做?