题目
要完成一门课程,学生必须通过四门考试(考试编号:1、2、3和4)。
给定一个包含学生参加的所有考试数据的表 exam_scores
,创建一个新表来跟踪每个学生的分数。
注意:学生只参加了每门考试一次。
示例:
对于给定的输入:
student_id |
student_name |
exam_id |
score |
---|---|---|---|
100 | Anna |
1 | 71 |
100 | Anna |
2 | 72 |
100 | Anna |
3 | 73 |
100 | Anna |
4 | 74 |
101 | Brian |
1 | 65 |
期望的输出应为:
student_name |
exam_1 |
exam_2 |
exam_3 |
exam_4 |
---|---|---|---|---|
Anna |
71 | 72 | 73 | 74 |
Brian |
65 | NULL | NULL | NULL |
输入:
exam_scores
表
列名 | 类型 |
---|---|
student_id |
INTEGER |
student_name |
VARCHAR |
exam_id |
INTEGER |
score |
INTEGER |
输出:
列名 | 类型 |
---|---|
student_name |
VARCHAR |
exam_1 |
INT |
exam_2 |
INT |
exam_3 |
INT |
exam_4 |
INT |
答案
解题思路
首先,我们需要将每位学生的成绩根据考试编号转换成不同的列。可以使用条件聚合来实现这一目标。
答案代码
SELECT
student_name,
MAX(CASE WHEN exam_id = 1 THEN score END) AS exam_1,
MAX(CASE WHEN exam_id = 2 THEN score END) AS exam_2,
MAX(CASE WHEN exam_id = 3 THEN score END) AS exam_3,
MAX(CASE WHEN exam_id = 4 THEN score END) AS exam_4
FROM
exam_scores
GROUP BY
student_name;
MAX(CASE WHEN exam_id = 1 THEN score END) AS exam_1
: 这一行将会根据exam_id
的值,将相应的score
分配到exam_1
列中。对于每位学生,会在对应的exam_id
下填入相应的分数。GROUP BY student_name
: 这一行确保了结果按照学生姓名分组。
MAX + Case When
MAX函数和CASE WHEN语句的组合可以实现一些有趣的功能,特别是在需要根据条件计算最大值时。以下是一些例子:
根据条件计算最大值:使用CASE WHEN语句在MAX函数内部,可以根据条件计算最大值。例如,假设有一个员工表,需要找出每个部门中工资最高的员工,可以这样做:
SELECT department, MAX(CASE WHEN salary >= 50000 THEN salary ELSE 0 END) AS max_salary FROM employees GROUP BY department;
这将返回每个部门中工资超过50000的员工的最高工资,如果没有满足条件的员工,则返回0。
返回满足条件的最大值:有时候我们想要找出满足特定条件的最大值,可以结合MAX和CASE WHEN来实现。例如,找出每个部门中工龄最高的员工的入职日期:
SELECT department, MAX(CASE WHEN age >= 5 THEN hire_date ELSE NULL END) AS max_hire_date FROM employees GROUP BY department;
这将返回每个部门中工龄超过5年的员工的入职日期,如果没有满足条件的员工,则返回NULL。
计算满足条件的行数:结合MAX和CASE WHEN,还可以计算满足特定条件的行数。例如,计算每个部门中工资超过50000的员工数量:
SELECT department, MAX(CASE WHEN salary >= 50000 THEN 1 ELSE 0 END) AS num_high_salary_employees FROM employees GROUP BY department;
这将返回每个部门中工资超过50000的员工的数量。
更多详细答案可关注公众号查阅。