如何在 MySQL 中将列转换为行以进行报告？

MySQL 数据透视：将列转换为行

在 MySQL 等关系数据库中，为了报表目的，经常需要转换数据。其中一种转换涉及将列转换为行，这个过程称为“透视”。此技术允许更灵活和更简洁的数据表示。

问题：

考虑以下 MySQL 表：

<code class="language-sql">CREATE TABLE mytable (
  id INT, 
  month VARCHAR(3), 
  col1 VARCHAR(1), 
  col2 VARCHAR(1), 
  col3 VARCHAR(1), 
  col4 VARCHAR(1)
);

INSERT INTO mytable (id, month, col1, col2, col3, col4) VALUES
(101, 'Jan', 'A', 'B', NULL, 'B'),
(102, 'Feb', 'C', 'A', 'G', 'E');</code>

目标是创建一个报表，将 col1 到 col4 的值作为行显示，月份 Jan 和 Feb 作为列。

解决方案：

MySQL 没有提供用于反透视或透视数据的内置函数。但是，我们可以使用 UNION ALL 和带 CASE 表达式的聚合来模拟这些操作。

1. 反透视：

为了反透视数据，我们创建一个新的子查询，使用 UNION ALL 将所有列中的行组合到单个列中：

<code class="language-sql">SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
UNION ALL
SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
UNION ALL
SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
UNION ALL
SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
FROM mytable;</code>

2. 透视：

接下来，我们将反透视查询包装在一个子查询中，并使用聚合和 CASE 语句将数据转换为所需的格式：

<code class="language-sql">SELECT descrip, 
  MAX(CASE WHEN month = 'Jan' THEN `value` ELSE NULL END) AS Jan,
  MAX(CASE WHEN month = 'Feb' THEN `value` ELSE NULL END) AS Feb
FROM (
  SELECT id, month, `value`, descrip
  FROM (
    SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
    UNION ALL
    SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
    UNION ALL
    SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
    UNION ALL
    SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
    FROM mytable
  ) AS unpivoted
) AS src
GROUP BY descrip;</code>

结果：

Descrip	Jan	Feb
col1	A	C
col2	B	A
col3	NULL	G
col4	B	E

注意：将 ELSE 0 END 改为 ELSE NULL END，使结果更符合数据库的规范，避免出现不必要的0值。

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