优化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的学生参加了class 1，因此在Class 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

这里有一个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;

Answer 2

有关将根据表定义和数据生成并可选地运行查询的存储过程，请参见。