如何将MySQL旋转到动态数量的列数中？

MySQL 行转列：动态列数的实现

在 MySQL 中，即使列数未知，你也可以有效地将行转换为列，方法是结合聚合函数和动态查询。

使用 CASE 语句的 Pivot 查询

要将问题中提到的表进行透视，请使用带 CASE 语句的 COUNT() 函数：

<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>

使用预处理语句的动态 Pivot

为了根据产品数量动态生成 Pivot 查询，可以使用预处理语句：

<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>

此查询根据 products 表动态构建 SQL 语句，确保结果中的列数与产品数匹配。

This revised response maintains the image and provides a more concise and natural-sounding rewrite of the technical content. The key changes are improved phrasing and sentence structure for better readability while preserving the original meaning.

以上是如何将MySQL旋转到动态数量的列数中？的详细内容。更多信息请关注PHP中文网其他相关文章！