MySQL中CASE语句优化及替换显示值的技术问询

现有qa表，原查询通过CASE语句+窗口函数实现：只要学生参加过某班级，该学生所有行的对应班级列就显示1。现在需要完成两个优化：

• 需求1：把显示的1替换为该学生对应班级的enrollment_date
• 需求2：优化重复的CASE语句写法，实现新增班级时自动生成对应列，不用手动修改查询语句

示例表结构与数据 #

CREATE TABLE qa(
    student_id INT,
    class VARCHAR(20),
    class_end_date DATE,
    enrollment_date DATE
);

INSERT INTO qa (student_id, class, class_end_date, enrollment_date) 
VALUES 
(1, 'class 1', '2022-03-03', '2022-02-14'),
(1, 'class 3', '2022-06-13', '2022-04-12'),
(1, 'class 4', '2022-07-03', '2022-06-19'),
(2, 'class 1', '2023-03-03', '2022-07-14'),
(2, 'class 2', '2022-08-03', '2022-07-17'),
(4, 'class 4', '2023-03-03', '2022-12-14'),
(4, 'class 2', '2022-04-03', '2022-03-21')
;

原查询语句 #

SELECT 
    *,
    CASE WHEN (SUM(CASE WHEN class = 'class 1' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 1',
    CASE WHEN (SUM(CASE WHEN class = 'class 2' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 2',
    CASE WHEN (SUM(CASE WHEN class = 'class 3' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 3',
    CASE WHEN (SUM(CASE WHEN class = 'class 4' THEN 1 END) OVER(PARTITION BY student_id)) >= 1 THEN 1 ELSE 0  END AS 'Class 4'
FROM qa;

解决方案 #

需求1：替换1为对应班级的enrollment_date #

原语句用SUM判断是否参加班级，其实可以直接用MAX()窗口函数，直接提取该学生对应班级的报名日期，没参加过的话显示NULL（如果需要显示0可以用COALESCE处理）。优化后的静态查询如下：

SELECT 
    *,
    MAX(CASE WHEN class = 'class 1' THEN enrollment_date END) OVER(PARTITION BY student_id) AS 'Class 1',
    MAX(CASE WHEN class = 'class 2' THEN enrollment_date END) OVER(PARTITION BY student_id) AS 'Class 2',
    MAX(CASE WHEN class = 'class 3' THEN enrollment_date END) OVER(PARTITION BY student_id) AS 'Class 3',
    MAX(CASE WHEN class = 'class 4' THEN enrollment_date END) OVER(PARTITION BY student_id) AS 'Class 4'
FROM qa;

原理：窗口函数按student_id分组，MAX会自动取该学生对应班级的报名日期（如果有记录），没有则返回NULL，比原语句少一次判断，更简洁高效。

需求2：自动适配新增班级的动态SQL #

静态写法每次加班级都要改语句，要实现自动生成列，需要用动态SQL，不同数据库写法略有差异，下面提供两种主流数据库的实现方式：

MySQL版本

SET @sql = NULL;

-- 自动获取所有班级，拼接成CASE语句列
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN class = ''', class, ''' THEN enrollment_date END) OVER(PARTITION BY student_id) AS ''', class, ''''
    )
) INTO @sql
FROM qa;

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT *, ', @sql, ' FROM qa;');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server版本

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN class = ''', class, ''' THEN enrollment_date END) OVER(PARTITION BY student_id) AS ', QUOTENAME(class)
    ),
    ', '
)
FROM (SELECT DISTINCT class FROM qa) AS classes;

SET @sql = N'SELECT *, ' + @sql + N' FROM qa;';

EXEC sp_executesql @sql;

原理：先从表中获取所有不重复的班级名称，自动拼接出每个班级对应的窗口函数列，再组装成完整查询语句执行。这样新增班级后，不需要修改SQL，直接执行动态语句就能自动生成新的班级列。

内容的提问来源于stack exchange，提问作者weizer