Optimieren Sie die Verwendung von CASE-Anweisungen in MySQL

Question

Hallo zusammen,

Ich habe eine Tabelle wie im Screenshot oben gezeigt. Ich habe eine Abfrage mit der Anweisung CASE geschrieben, damit sie die zusätzlichen Spalten zurückgibt, die ich benötigte. Hier ist die Abfrage, die ich geschrieben habe:

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'
VON qa;

Dies ist die Ergebnistabelle, die ich erhalte:

Was ich mit dieser Abfrage erreichen möchte, ist, dass, wenn ein Student den Kurs besucht hat, 1 in der Klassenspalte für alle Zeilen angezeigt wird, die zu diesem Studenten gehören.

Zum Beispiel ist ein Schüler mit student_id in der Klasse 1 In der Spalte ;/code> steht in beiden Zeilen für 2

Ich habe in der Abfrage erreicht, was ich will, aber jetzt möchte ich, dass es sich um das enrollment_date handelt. Hier ist die endgültige Ausgabe, die ich möchte:

Darf ich wissen, wie ich die Abfrage ändern muss, um die endgültige Ausgabe im Screenshot oben zu erhalten?

Zweite Frage:

Wie Sie in der obigen Abfrage sehen können, verfügt jede Klasse über eine separate CASE-Anweisung, um Spalten für diese Klasse zu erstellen. Allerdings kann es in Zukunft 5,6,7,... Klassen geben, sodass ich immer dann, wenn es andere neue Klassen gibt, erneut zusätzliche CASE-Anweisungen hinzufügen muss.Gibt es überhaupt eine Möglichkeit, meine Abfrage so zu optimieren, dass ich nicht vier CASE-Anweisungen für vier verschiedene Klassen benötige, aber trotzdem Spalten für verschiedene Klassen erstellen kann (wenn es eine neue Klasse gibt). , wird es auch neue Spaltenklassen geben)?

Beispieldaten

Tabelle qa erstellen(
    student_id INT,
    Klasse varchar(20),
    class_end_date Datum,
    enrollment_date Datum
);

In QA einfügen (Studenten-ID, Klasse, Klassenenddatum, Einschreibungsdatum)
Werte
(1, „Klasse 1“, „2022-03-03“, „2022-02-14“),
(1, „Klasse 3“, „13.06.2022“, „12.04.2022“),
(1, „Klasse 4“, „2022-07-03“, „2022-06-19“),
(2, „Klasse 1“, „2023-03-03“, „2022-07-14“),
(2, „Klasse 2“, „2022-08-03“, „2022-07-17“),
(4, „Klasse 4“, „2023-03-03“, „2022-012-14“),
(4, „Klasse 2“, „03.04.2022“, „21.03.2022“)
;

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;