MySQL中的行转列和列转行

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

1. 行转列（Pivot）

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

1.1 示例数据

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

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

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，记录了每个月的销售数据：

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

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 中进行复杂的数据变换和分析。