Bagaimana untuk Menyahpivot Data dengan Lajur Dinamik dalam MySQL Apabila Anda Kekurangan Fungsi UNPIVOT?

Menyahpivot Data dengan Lajur Dinamik

Dalam alam transformasi pertanyaan, data nyahpangsi memainkan peranan penting dalam membentuk semula struktur jadual. Satu senario sedemikian melibatkan pemindahan lajur dinamik ke dalam baris, tugas yang kekurangan fungsi UNPIVOT MySQL boleh dihalang.

Masalah: Memindahkan Lajur Dinamik

Bayangkan jadual yang dipanggil Table_1 dengan lajur dinamik (abc, brt, ccc, dsb.) yang mewakili atribut yang berbeza, seperti yang ditunjukkan di bawah:

Table_1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

Matlamatnya adalah untuk mengubah Jadual_1 menjadi Jadual_Keputusan_Dijangka yang menyenaraikan lajur sebagai baris, dengan hanya nilai bukan sifar:

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

Penyelesaian: Dynamic UNION ALL dan Penyata Disediakan

Untuk mencapai ini transformasi, kami boleh memanfaatkan gabungan UNION ALL dan pernyataan yang disediakan untuk mengendalikan lajur dinamik:

1. Jana SQL Dinamik: Menggunakan information_schema.columns, kami boleh menjana SQL dinamik yang memilih baris untuk setiap lajur bukan sifar.
2. Sediakan Penyata: Kami menggunakan pernyataan yang disediakan untuk melaksanakan SQL dinamik dan mengelakkan potensi kelemahan suntikan SQL.
3. Melaksanakan Pernyataan: Kami melaksanakan pernyataan yang disediakan untuk mendapatkan semula data yang tidak dipivot.

Berikut ialah kod untuk dinamik penyelesaian:

-- Generate dynamic SQL
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

-- Build the final SQL query
SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

-- Prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Keputusan:

Jadual yang terhasil akan mengandungi baris yang dikehendaki:

Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

Atas ialah kandungan terperinci Bagaimana untuk Menyahpivot Data dengan Lajur Dinamik dalam MySQL Apabila Anda Kekurangan Fungsi UNPIVOT?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!