如何在 MySQL 中將行轉換為動態列？

MySQL：將行數據轉換為動態列

問題描述

假設我們有以下三個MySQL表：

• 產品表 (Products):

  <code>  id | name
     1 | 产品A
     2 | 产品B</code>

• 合作夥伴表 (Partners):

  <code>  id | name
     1 | 合作伙伴A
     2 | 合作伙伴B</code>

• 銷售表 (Sales):

  <code>  partners_id | products_id
              1             2
              2             5
              1             5
              1             3
              1             4
              1             5
              2             2
              2             4
              2             3
              1             1</code>

目標是將銷售表中的行數據轉換為動態列，列名代表不同的產品。期望的輸出結果如下：

<code>partner_name | 产品A | 产品B | 产品C | 产品D | 产品E
合作伙伴A              1           1           1           1           2
合作伙伴B              0           1           1           1           1</code>

解答

不幸的是，MySQL 缺少專門的 PIVOT 函數。但是，我們可以結合聚合函數和 CASE 語句來實現類似的結果：

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = '产品A' THEN 1 END) AS 产品A,
  COUNT(CASE WHEN pd.product_name = '产品B' THEN 1 END) AS 产品B,
  COUNT(CASE WHEN pd.product_name = '产品C' THEN 1 END) AS 产品C,
  COUNT(CASE WHEN pd.product_name = '产品D' THEN 1 END) AS 产品D,
  COUNT(CASE WHEN pd.product_name = '产品E' THEN 1 END) AS 产品E
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

動態列轉換

為了處理未知數量的產品，我們需要使用動態列轉換。這需要在運行時使用產品表中的信息來準備 SQL 語句：

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;

這種方法允許處理任意數量的產品列，並確保查詢能夠適應產品表中的變化，而無需修改 SQL 語句本身。

以上是如何在 MySQL 中將行轉換為動態列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！