Bagaimana untuk Mengubah Data Lajur MySQL ke Baris: Pendekatan Pertanyaan SQL?

Mengasingkan Data Lajur MySQL sebagai Baris

Apabila bekerja dengan MySQL, tugas biasa ialah mengubah data daripada format lajur kepada format baris . Proses ini, yang dikenali sebagai pivoting, boleh berguna terutamanya untuk mempersembahkan data dengan cara yang lebih mesra pengguna dan mudah dihadam.

Pertimbangkan senario berikut:

Anda mempunyai tiga jadual:

• Jadual soalan (id, soalan)
• Jadual keputusan (id, user_id, job_id)
• Jadual jawapan (id, jawapan, fk_question_id, fk_result_id)

Jadual Soalan mengandungi senarai soalan yang mungkin. Jadual Keputusan mengaitkan pengguna dengan hasil kerja. Jadual Jawapan menyimpan jawapan pengguna kepada soalan untuk setiap keputusan.

Matlamat anda ialah untuk memaparkan jawapan soalan sebagai lajur bagi setiap set keputusan. Hasil yang diinginkan adalah seperti berikut:

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

Untuk mencapai transformasi ini, anda boleh menggunakan pertanyaan MySQL berikut:

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

Pertanyaan ini menggunakan gabungan pernyataan INNER JOIN dan MAX( ) dan CASE berfungsi untuk mengagregatkan jawapan bagi setiap soalan dan kombinasi keputusan. Ia secara berkesan menggerakkan data daripada format lajur kepada format baris, membolehkan anda menggambarkan jawapan sebagai lajur.

Untuk melaksanakan pertanyaan ini, gantikan nama jadual dan lajur dalam klausa FROM dan JOIN dengan jadual sebenar anda dan nama lajur. Setelah dilaksanakan, pertanyaan akan mengembalikan data terpivot dalam format yang diingini.

Sebagai alternatif, jika anda mempunyai bilangan soalan yang banyak, anda boleh menggunakan versi SQL dinamik pertanyaan untuk mengendalikan bilangan soalan yang tidak diketahui. Pendekatan dinamik ini amat berguna apabila bilangan lajur dalam hasil pivoted tidak diketahui atau sering berubah. Versi SQL dinamik adalah seperti berikut:

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;

Atas ialah kandungan terperinci Bagaimana untuk Mengubah Data Lajur MySQL ke Baris: Pendekatan Pertanyaan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!