如何在 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中文网其他相关文章！