Bagaimana untuk Mengubah Data Lajur MySQL ke Baris Menggunakan Penyata MAX() dan CASE?

Memaparkan Data Lajur Jadual Pangsi MySQL sebagai Baris

Menukar data lajur jadual kepada baris boleh menjadi tugas yang mencabar, terutamanya untuk pelbagai dimensi set data. Dalam MySQL, operasi ini biasanya dikenali sebagai "pivoting."

Masalah:

Dataset data yang disediakan terdiri daripada tiga jadual:

• Soalan: Menyimpan soalan tajuk.
• Keputusan: Merekodkan respons pengguna kepada ID kerja tertentu.
• Jawapan: Mengandungi jawapan untuk setiap soalan dan gabungan hasil.

Matlamatnya adalah untuk menyusun semula data untuk mempamerkan jawapan soalan sebagai lajur untuk setiap keputusan set.

Penyelesaian:

Untuk mencapai matlamat ini, kami boleh menggunakan pertanyaan MySQL yang memanfaatkan fungsi agregat MAX() bersama-sama dengan pernyataan CASE. Pertanyaan di bawah secara berkesan menggerakkan data lajur ke dalam baris:

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

Penjelasan:

• Pertanyaan memilih pelbagai medan daripada jadual Keputusan (a), termasuk ID keputusan, ID pengguna dan ID kerja.
• Ia kemudian menggunakan INNER JOIN untuk mendapatkan semula yang sepadan jawapan daripada jadual Jawapan (b).
• Satu lagi INNER JOIN pautan ke Jadual Soalan (c) untuk mengenal pasti soalan sebenar bagi setiap jawapan.
• Fungsi MAX() digunakan dalam CASE penyata untuk mengagregatkan jawapan bagi setiap hasil dan gabungan soalan.
• Akhir sekali, keputusan dikumpulkan mengikut ID hasil, ID pengguna dan ID kerja untuk mencipta struktur baris yang diingini.

Pertanyaan ini menghasilkan output berikut:

Versi Dinamik:

Jika anda mempunyai nombor yang tidak diketahui daripada soalan (cth., 1000), versi dinamik pertanyaan adalah lebih sesuai. Ini boleh dicapai menggunakan SQL dinamik dan gabungan pembolehubah:

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;

Pertanyaan dinamik ini menggunakan gabungan pembolehubah untuk membina pernyataan SQL secara dinamik berdasarkan kandungan jadual Soalan. Hasilnya ialah pertanyaan yang lebih fleksibel yang boleh mengendalikan sebarang bilangan soalan.

Atas ialah kandungan terperinci Bagaimana untuk Mengubah Data Lajur MySQL ke Baris Menggunakan Penyata MAX() dan CASE?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!