Bagaimana untuk Memutar Jadual Secara Dinamik dalam MySQL?

Pangsi dinamik MySQL: tukar baris kepada lajur

Dalam MySQL, anda mungkin memerlukan jadual pangsi untuk menukar baris kepada lajur. Ini amat berguna apabila anda perlu mewakili data dalam orientasi yang berbeza. Walaupun MySQL tidak menyediakan fungsi PIVOT secara asli, terdapat beberapa cara untuk menyelesaikan tugas ini.

Cabaran: Jadual Pangsi Dinamik

Andaikan anda mempunyai tiga jadual: Produk, Rakan Kongsi dan Jualan. Anda perlu membuat jadual dengan rakan kongsi sebagai baris dan produk sebagai lajur dan mengira kuantiti jualan yang dikaitkan dengan setiap gabungan.

Gunakan pernyataan KES

Salah satu cara ialah menggunakan fungsi agregat dengan pernyataan CASE. Dengan mencipta berbilang penyata CASE, anda boleh mengira jualan untuk setiap produk dan mengumpulkan hasil mengikut nama rakan kongsi.

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS ProductE
FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name;

Gunakan pernyataan yang disediakan untuk melaksanakan perspektif dinamik

Untuk situasi di mana bilangan produk tidak diketahui dan berubah secara dinamik, anda boleh menggunakan pernyataan yang disediakan. Dengan menjana pertanyaan SQL secara dinamik, anda boleh melaraskan bilangan lajur berdasarkan produk yang tersedia.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN Product_Name = ''',
      Product_Name,
      ''' THEN 1 END) AS ',
      REPLACE(Product_Name, ' ', '')
    )
  ) INTO @sql
FROM products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name');

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

Sekatan dan Nota

Perhatikan bahawa GROUP_CONCAT had bait lalai ialah 1024. Jika rentetan bercantum melebihi had ini, anda mungkin perlu menggunakan SET @@group_concat_max_len = 32000; untuk meningkatkan had.

Atas ialah kandungan terperinci Bagaimana untuk Memutar Jadual Secara Dinamik dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!