Wie kann ich MySQL-Spaltendaten mit MAX()- und CASE-Anweisungen in Zeilen umwandeln?

Spaltendaten einer MySQL-Pivot-Tabelle als Zeilen anzeigen

Das Konvertieren der Spaltendaten einer Tabelle in Zeilen kann eine anspruchsvolle Aufgabe sein, insbesondere bei mehrdimensionalen Tabellen Datensätze. In MySQL wird dieser Vorgang allgemein als „Pivotieren“ bezeichnet.

Problem:

Der bereitgestellte Datensatz besteht aus drei Tabellen:

• Frage:Speichert Frage Titel.
• Ergebnisse: Zeichnet Benutzerantworten auf bestimmte Job-IDs auf.
• Antworten: Enthält die Antworten für jede Frage- und Ergebniskombination.

Ziel ist es, die Daten neu zu strukturieren, um die Fragenantworten als Spalten für jedes Ergebnis anzuzeigen gesetzt.

Lösung:

Um dies zu erreichen, können wir eine MySQL-Abfrage verwenden, die die Aggregatfunktion MAX() in Verbindung mit CASE-Anweisungen nutzt. Die folgende Abfrage wandelt die Spaltendaten effektiv in Zeilen um:

SELECT  a.ID,
        a.user_ID,
        a.job_id,
        MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?',
        MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?',
        MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? '
FROM    Results a
        INNER JOIN Answers b
            ON a.id = b.fk_result_id
        INNER JOIN Question c
            ON b.fk_question_id = c.ID
GROUP   BY a.ID,
        a.user_ID,
        a.job_id

Erklärung:

• Die Abfrage wählt verschiedene Felder aus der Ergebnistabelle (a) aus. einschließlich der Ergebnis-ID, der Benutzer-ID und der Job-ID.
• Es verwendet dann einen INNER JOIN, um die entsprechenden Antworten aus den Antworten (b) abzurufen. Tabelle.
• Ein weiterer INNER JOIN verweist auf die Fragetabelle (c), um die eigentliche Frage für jede Antwort zu identifizieren.
• Die Funktion MAX() wird in CASE-Anweisungen angewendet, um die Antwort für jede Antwort zu aggregieren Kombination aus Ergebnis und Frage.
• Abschließend werden die Ergebnisse nach Ergebnis-ID, Benutzer-ID und Job-ID gruppiert, um die gewünschte Zeile zu erstellen Struktur.

Diese Abfrage liefert die folgende Ausgabe:

result_id	user_id	job_id	Is it this?	Or this?	Or that?
1	1	1	Yes	No	Maybe
2	1	3	Maybe	No	Maybe
3	2	3	Yes	Yes	No

Dynamische Version:

Wenn Sie eine unbekannte Anzahl von Fragen haben (z. B. 1000) ist eine dynamische Version der Abfrage besser geeignet. Dies kann mithilfe von dynamischem SQL und Variablenverkettung erreicht werden:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN c.question = ''',
      question,
      ''' then b.answer end) AS ',
      CONCAT('`',question,'`')
    )
  ) INTO @sql
FROM Question;

SET @sql = CONCAT('SELECT  a.ID,
                            a.user_ID,
                            a.job_id, ', @sql, ' 
                    FROM    Results a
                            INNER JOIN Answers b
                                ON a.id = b.fk_result_id
                            INNER JOIN Question c
                                ON b.fk_question_id = c.ID
                    GROUP   BY a.ID,
                            a.user_ID,
                            a.job_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Diese dynamische Abfrage verwendet Variablenverkettung, um die SQL-Anweisung dynamisch basierend auf dem Inhalt der Fragentabelle zu erstellen. Das Ergebnis ist eine flexiblere Abfrage, die eine beliebige Anzahl von Fragen verarbeiten kann.

Das obige ist der detaillierte Inhalt vonWie kann ich MySQL-Spaltendaten mit MAX()- und CASE-Anweisungen in Zeilen umwandeln?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!