Bagaimana untuk Menjana Lajur Secara Dinamik untuk Pivoting Baris ke Lajur dalam MySQL?

Penjanaan lajur dinamik baris ke lajur MySQL

Andaikan terdapat tiga jadual MySQL:

• jadual produk: menyimpan maklumat produk (id, nama).
• jadual rakan kongsi: menyimpan maklumat rakan kongsi (id, nama).
• jadual jualan: merekodkan urus niaga jualan (id_partners, products_id).

Matlamat kami adalah untuk menukar data jualan kepada jadual dengan rakan kongsi disenaraikan sebagai baris dan produk sebagai lajur dinamik.

Keputusan prapengumpulan

Menggunakan pertanyaan mudah, kita boleh mendapatkan jadual dengan nama rakan kongsi dan nama produk sebagai lajur, bersama-sama dengan bilangan kejadian:

SELECT partners.name AS partner_name, 
       products.name AS product_name, 
       COUNT(*) 
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id

Walau bagaimanapun, pendekatan ini tidak mempunyai penjanaan lajur dinamik untuk kuantiti produk yang berubah-ubah.

Pemusingan dinamik menggunakan CASE dan fungsi agregat

MySQL tidak mempunyai fungsi PIVOT khusus, jadi kita mesti menggunakan pernyataan CASE untuk pertanyaan agregat:

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

Kaedah ini boleh mengira bilangan kejadian setiap nama produk untuk setiap rakan kongsi secara dinamik.

Perspektif dinamik menggunakan pernyataan yang disediakan

Untuk jadual pangsi yang benar-benar dinamik, kami boleh menggunakan pernyataan yang disediakan untuk menjana rentetan pertanyaan SQL berdasarkan bilangan produk:

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;

Kaedah ini membolehkan kami menjana nama lajur dan kiraan agregat secara dinamik untuk setiap produk. Ingat untuk melaraskan had saiz GROUP_CONCAT jika perlu.

Atas ialah kandungan terperinci Bagaimana untuk Menjana Lajur Secara Dinamik untuk Pivoting Baris ke Lajur dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!