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