优化MySQL中CASE语句的使用

Question

大家好，

我有一个表格，如上面的屏幕截图所示。我使用 CASE 语句编写了一个查询，以便它将返回我需要的额外列。以下是我编写的查询：

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。

例如，具有 student_id 2 的学生参加了 1 班 ，因此在 1 班 列下，student_id 2 的两行都将显示 1。

我已经在查询中实现了我想要的效果，但现在我不使用 1，而是希望它成为该类的 enrollment_date。以下是我想要的最终输出：

我可以知道应该如何修改查询才能获得上面屏幕截图中的最终输出吗？

第二个问题：

正如您在上面的查询中看到的，每个类都分别有一个 CASE 语句，以便为该类创建列。但是，将来可能会有 5,6,7,... 类，因此每当存在不同的新类时，我都需要再次添加额外的 CASE 语句。无论如何，我是否可以优化我的查询，以便不需要为 4 个不同的类提供 4 个 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-012-14'),
(4, 'class 2', '2022-04-03', '2022-03-21')
;

Answer 1

请参阅Pivot了解存储过程，该存储过程将根据表定义和数据生成并[可选]运行查询。

Answer 2

这是一个同时包含 class_end_date 和 enrollment_date 的示例 -

SELECT 
    student_id,
    GROUP_CONCAT(IF(class = 'class 1', enrollment_date, null)) 'Class 1 Enrolled',
    GROUP_CONCAT(IF(class = 'class 1', class_end_date, null)) 'Class 1 End',
    GROUP_CONCAT(IF(class = 'class 2', enrollment_date, null)) 'Class 2 Enrolled',
    GROUP_CONCAT(IF(class = 'class 2', class_end_date, null)) 'Class 2 End',
    GROUP_CONCAT(IF(class = 'class 3', enrollment_date, null)) 'Class 3 Enrolled',
    GROUP_CONCAT(IF(class = 'class 3', class_end_date, null)) 'Class 3 End',
    GROUP_CONCAT(IF(class = 'class 4', enrollment_date, null)) 'Class 4 Enrolled',
    GROUP_CONCAT(IF(class = 'class 4', class_end_date, null)) 'Class 4 End'
FROM qa
GROUP BY student_id;