【数据分析面试】17. 学生分数表 （SQL:MAX函数的特殊使用技巧 + Case When）

题目

要完成一门课程，学生必须通过四门考试（考试编号：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语句的组合可以实现一些有趣的功能，特别是在需要根据条件计算最大值时。以下是一些例子：

1. 根据条件计算最大值：使用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。

2. 返回满足条件的最大值：有时候我们想要找出满足特定条件的最大值，可以结合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。

3. 计算满足条件的行数：结合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的员工的数量。

更多详细答案可关注公众号查阅。