如何在 MySQL 中动态透视表？

MySQL动态透视：将行转换为列

在MySQL中，您可能需要透视表，将行转换为列。当您需要以不同的方向表示数据时，这尤其有用。虽然MySQL本身并不提供PIVOT函数，但有几种方法可以完成此任务。

挑战：动态透视表

假设您有三个表：产品表、合作伙伴表和销售表。您需要创建一个表，以合作伙伴为行，产品为列，计算每个组合相关的销售数量。

使用CASE语句

一种方法是使用带CASE语句的聚合函数。通过创建多个CASE语句，您可以计算每种产品的销售额，并按合作伙伴名称分组结果。

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;

使用预处理语句实现动态透视

对于产品数量未知且动态变化的情况，您可以使用预处理语句。通过动态生成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;

限制和注意事项

需要注意的是，GROUP_CONCAT 默认字节限制为 1024。如果连接的字符串超过此限制，您可能需要使用 SET @@group_concat_max_len = 32000; 来增加限制。

以上是如何在 MySQL 中动态透视表？的详细内容。更多信息请关注PHP中文网其他相关文章！