如何将 MySQL 行转换为基于多列的列？

MySQL 查询基于多列将行转换为列

简介：

动态将行转换为列是数据操作中的常见任务。在本文中，我们探索使用 MySQL 查询的解决方案，扩展了处理单列转换的先前解决方案的功能。

问题：

您有一个具有多行表示数据和相应价格的表，按订单和项目组织。目标是将这些数据转换为一个表，其中每个订单表示为一列，每个商品表示为一行，并显示相应的价格。

解决方案：

为了实现这种多列转换，我们采用了两步过程。

第 1 步：取消透视数据

我们首先使用 UNION ALL 反转数据，为每个订单和商品组合创建多行。这使我们能够在下一步中更轻松地操作数据。

SELECT id, CONCAT('order', `order`) col, data value
FROM tableA
UNION ALL
SELECT id, CONCAT('item', item) col, price value
FROM tableA;

第 2 步：对未透视的数据进行透视

一旦对数据进行了透视，我们就进行透视使用带有 CASE 语句的聚合函数将其返回到列中。这将按 ID 对未透视的行进行分组，并将相应的值分配给适当的列。

SELECT
  id, 
  MAX(CASE WHEN col = 'order1' THEN value END) ORDER1,
  MAX(CASE WHEN col = 'order2' THEN value END) ORDER2,
  MAX(CASE WHEN col = 'order3' THEN value END) ORDER3,
  MAX(CASE WHEN col = 'item1' THEN value END) ITEM1,
  MAX(CASE WHEN col = 'item2' THEN value END) ITEM2,
  MAX(CASE WHEN col = 'item3' THEN value END) ITEM3
FROM
(
  SELECT id, CONCAT('order', `order`) col, data value
  FROM tableA
  UNION ALL
  SELECT id, CONCAT('item', item) col, price value
  FROM tableA
) d
GROUP BY id;

动态查询生成：

处理具有不同订单数量的表和项目，我们可以使用准备好的动态生成查询

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN col = ''',
      col,
      ''' THEN value END) AS `', 
      col, '`')
  ) INTO @sql
FROM
(
  SELECT CONCAT('order', `order`) col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) col
  FROM tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM
                  (
                    SELECT id, CONCAT(''order'', `order`) col,  data value
                    FROM tableA
                    UNION ALL
                    SELECT id, CONCAT(''item'', item) col, price value
                    FROM tableA
                  ) d
                  GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结果：

结果表提供了数据的简明表示，每个订单作为一列，每个项目作为一行，显示相应的价格。

以上是如何将 MySQL 行转换为基于多列的列？的详细内容。更多信息请关注PHP中文网其他相关文章！