MySQL中的行转列和列转行

原创

用户3672714

发布于 2025-09-06 14:51:23

1.1K0

在 MySQL 中，行转列（Row to Column） 和 列转行（Column to Row） 是常见的操作，用于将数据以不同的形式进行展示。通常，行转列用于将多个行的数据合并成一行，而列转行则将一行数据拆分成多行。以下是如何在 MySQL 中实现这两种操作的详细解释。

1. 行转列（Pivot）

行转列是将表中的行数据转换成列形式。MySQL 本身没有直接的 PIVOT 操作，但可以通过 条件聚合（Conditional Aggregation） 和 CASE 语句实现。

1.1 示例数据

假设有一个表 sales，记录了不同月份的销售情况，如下所示：

product_id	month	sales
1	Jan	100
1	Feb	150
1	Mar	120
2	Jan	200
2	Feb	180
2	Mar	220

我们希望将每个产品的每个月的销售数据行转列，如下所示：

product_id	Jan_sales	Feb_sales	Mar_sales
1	100	150	120
2	200	180	220

1.2 行转列的实现方式

可以使用 CASE 和聚合函数（如 SUM()）来实现：

SELECT

product_id,

SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan_sales,

SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb_sales,

SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar_sales

FROM

sales

GROUP BY

product_id;

解释：

CASE 语句：根据不同的月份给出不同的销售值，如果是目标月份则返回 sales 值，否则返回 0。
SUM() 聚合函数：聚合相同 product_id 下的销售数据。
GROUP BY：按照 product_id 分组，生成每个产品的销售数据。

2. 列转行（Unpivot）

列转行是将列的数据转换成行。MySQL 可以通过 UNION ALL 来实现列转行操作。假设我们有以下表 sales，记录了每个月的销售数据：

product_id	Jan_sales	Feb_sales	Mar_sales
1	100	150	120
2	200	180	220

我们希望将每个月的销售数据列转行，结果如下：

product_id	month	sales
1	Jan	100
1	Feb	150
1	Mar	120
2	Jan	200
2	Feb	180
2	Mar	220

2.1 列转行的实现方式

可以使用 UNION ALL 来将每个月的数据从列转成行：

SELECT product_id, 'Jan' AS month, Jan_sales AS sales FROM sales

UNION ALL

SELECT product_id, 'Feb' AS month, Feb_sales AS sales FROM sales

UNION ALL

SELECT product_id, 'Mar' AS month, Mar_sales AS sales FROM sales;

解释：

使用 UNION ALL 将每个月的销售数据提取出来，并为每个月的数据创建一个 month 列。
SELECT product_id, 'Jan' AS month, Jan_sales AS sales：这里将 Jan_sales 列的值提取出来，并且通过 'Jan' 给每一行指定月份。
对 Feb_sales 和 Mar_sales 使用相同的方法。

3. 使用动态 SQL 实现通用的行转列和列转行

对于动态的场景（例如表的列数或者行数不固定），需要使用动态 SQL 来生成查询语句。这里我们简单介绍一下如何使用 PREPARE 和 EXECUTE 来构建动态 SQL。

3.1 动态行转列

假设我们有多个产品和月份，但数量未知，可以通过动态 SQL 来生成行转列查询：

SET @sql = NULL;

SELECT GROUP_CONCAT(

DISTINCT CONCAT(

'SUM(CASE WHEN month = "', month, '" THEN sales ELSE 0 END) AS ', month, '_sales'

)

) INTO @sql

FROM sales;

SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

3.2 动态列转行

如果需要将列转行，同样可以通过 UNION ALL 和动态 SQL 实现：

SET @sql = NULL;

SELECT GROUP_CONCAT(

DISTINCT CONCAT(

'SELECT product_id, "', month, '" AS month, ', month, '_sales AS sales FROM sales'

)

) INTO @sql

FROM sales;

SET @sql = CONCAT(@sql, ';');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

4. 总结

行转列（Pivot）：通过使用 CASE 和聚合函数（如 SUM()）实现。
列转行（Unpivot）：通过使用 UNION ALL 将每一列数据转换成独立的行。
动态 SQL：对于列数或行数不固定的情况，可以使用动态 SQL 来构建查询。

这两种操作在数据报表生成、统计分析和数据处理过程中都非常常见。掌握了这些方法后，你就能轻松地在 MySQL 中进行复杂的数据变换和分析。

https://www.52runoob.com/archives/6791

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

登录后参与评论

0 条评论

热度