Wie wandele ich MySQL-Spaltendaten in Zeilen um: Ein SQL-Abfrageansatz?

MySQL-Spaltendaten als Zeilen umwandeln

Bei der Arbeit mit MySQL besteht eine häufige Aufgabe darin, Daten aus einem Spaltenformat in ein Zeilenformat umzuwandeln . Dieser als Pivotieren bezeichnete Vorgang kann besonders nützlich sein, um Daten benutzerfreundlicher und leichter verständlich darzustellen.

Stellen Sie sich das folgende Szenario vor:

Sie haben drei Tabellen:

• Fragentabelle (ID, Frage)
• Ergebnistabelle (ID, Benutzer_ID, job_id)
• Antworttabelle (id, Antwort, fk_question_id, fk_result_id)

Die Fragentabelle enthält eine Liste möglicher Fragen. Die Ergebnistabelle ordnet Benutzer Jobergebnissen zu. In der Antworttabelle werden Benutzerantworten auf die Fragen für jedes Ergebnis gespeichert.

Ihr Ziel ist es, die Antworten auf die Fragen als Spalten für jeden Ergebnissatz anzuzeigen. Das gewünschte Ergebnis ist wie folgt:

+-----------+---------+--------+-------------+----------+----------+
| 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       |
+-----------+---------+--------+-------------+----------+----------+

Um diese Transformation zu erreichen, können Sie die folgende MySQL-Abfrage verwenden:

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

Diese Abfrage verwendet eine Kombination aus INNER JOIN-Anweisungen und MAX( ) und CASE-Funktionen zum Aggregieren der Antworten für jede Frage- und Ergebniskombination. Dadurch werden die Daten effektiv von einem Spaltenformat in ein Zeilenformat umgewandelt, sodass Sie die Antworten als Spalten visualisieren können.

Um diese Abfrage auszuführen, ersetzen Sie die Tabellen- und Spaltennamen in den FROM- und JOIN-Klauseln durch Ihre tatsächliche Tabelle und Spaltennamen. Nach der Ausführung gibt die Abfrage die Pivot-Daten im gewünschten Format zurück.

Wenn Sie eine große Anzahl von Fragen haben, können Sie alternativ eine dynamische SQL-Version der Abfrage verwenden, um eine unbekannte Anzahl von Fragen zu bearbeiten. Dieser dynamische Ansatz ist besonders nützlich, wenn die Anzahl der Spalten im Pivot-Ergebnis unbekannt ist oder sich häufig ändert. Die dynamische SQL-Version lautet wie folgt:

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;

Das obige ist der detaillierte Inhalt vonWie wandele ich MySQL-Spaltendaten in Zeilen um: Ein SQL-Abfrageansatz?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!