如何在 MySQL 中動態產生行到列透視的列？

MySQL 行轉列動態列產生

假設有三個 MySQL 表：

• products 表: 儲存產品資訊 (id, name)。
• partners 表: 儲存合作夥伴資訊 (id, name)。
• sales 表: 記錄銷售交易 (partners_id, products_id)。

我們的目標是將銷售資料轉換為一個表，其中合作夥伴列在行中，產品作為動態列。

預聚合結果

使用簡單的查詢，我們可以得到一個表，其中包含合作夥伴名稱和產品名稱作為列，以及出現的次數：

<code class="language-sql">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</code>

然而，這種方法缺乏針對可變產品數量的動態列產生。

使用 CASE 和聚合函數的動態透視

MySQL 缺少專用的 PIVOT 函數，因此我們必須使用 CASE 語句進行聚合查詢：

<code class="language-sql">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</code>

這種方法可以動態地計算每個合作夥伴每個產品名稱出現的次數。

使用預處理語句的動態透視

對於真正的動態透視表，我們可以利用預處理語句根據產品的數量產生 SQL 查詢字串：

<code class="language-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;</code>

此方法允許我們動態產生列名並聚合每個產品的計數。如有必要，請記得調整 GROUP_CONCAT 的大小限制。

以上是如何在 MySQL 中動態產生行到列透視的列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！