MySQL如何实现行转列和列转行？

在MySQL中，行转列（pivot）和列转行（unpivot）没有专门的函数，需要通过SQL技巧来实现： ## 一、行转列（Pivot） 行转列通常使用`CASE WHEN`语句聚合实现。 假设有如下数据： | name | subject | score | |------|---------|-------| | 张三 | 数学 | 80 | | 张三 | 语文 | 90 | | 李四 | 数学 | 85 | | 李四 | 语文 | 88 | 想转为： | name | 数学 | 语文 | |------|------|------| | 张三 | 80 | 90 | | 李四 | 85 | 88 | 实现方式： ```sql SELECT name, MAX(CASE subject WHEN '数学' THEN score END) AS '数学', MAX(CASE subject WHEN '语文' THEN score END) AS '语文' FROM student_score GROUP BY name; ``` ### 说明： - 使用`CASE WHEN`匹配不同的`subject`，再配合`MAX`聚合函数将多个行合并为一行。 - 如果有多列，只需继续添加`CASE WHEN`即可。 ## 二、列转行（Unpivot） 列转行通常使用`UNION ALL`实现。 假设原始数据： | name | 数学 | 语文 | |------|------|------| | 张三 | 80 | 90 | | 李四 | 85 | 88 | 想转为： | name | subject | score | |------|---------|-------| | 张三 | 数学 | 80 | | 张三 | 语文 | 90 | | 李四 | 数学 | 85 | | 李四 | 语文 | 88 | 实现方式： ```sql SELECT name, '数学' AS subject, 数学 AS score FROM student_score UNION ALL SELECT name, '语文' AS subject, 语文 AS score FROM student_score; ``` ### 说明： - 用多个`SELECT`语句，通过`UNION ALL`拼接数据。 - 明确指定转换后的列名和字段名即可。 ## 三、小结（精炼版） - **行转列**：使用`CASE WHEN` + 聚合函数（如`MAX`）+`GROUP BY` - **列转行**：使用多个`SELECT` + `UNION ALL`

在 MySQL 中，**行转列**（横向展开数据）和 **列转行**（纵向拆分数据）是数据转换的常见需求，通常用于数据报表和聚合统计。 ## **1. 行转列（Row to Column）** ### **案例** 假设 `sales` 表： | id | year | product | sales | |----|------|---------|-------| | 1 | 2020 | A | 100 | | 2 | 2020 | B | 200 | | 3 | 2021 | A | 150 | | 4 | 2021 | B | 300 | **目标转换** | year | A | B | |------|----|----| | 2020 | 100 | 200 | | 2021 | 150 | 300 | ### **实现方法** ✅ **方法 1：使用 `CASE WHEN` 实现** ```sql SELECT year, SUM(CASE WHEN product = 'A' THEN sales ELSE 0 END) AS A, SUM(CASE WHEN product = 'B' THEN sales ELSE 0 END) AS B FROM sales GROUP BY year ORDER BY year; ``` **解释** - **`CASE WHEN product = 'A' THEN sales ELSE 0 END`** - 仅当 `product='A'` 时，返回 `sales`，否则返回 `0`。 - **`SUM(...)`** - 对相同 `year` 的数据进行合计，形成新列 `A`、`B`。 ## **2. 列转行（Column to Row）** ### **案例** **原始表（列转行前）** | year | A | B | |------|----|----| | 2020 | 100 | 200 | | 2021 | 150 | 300 | **目标转换** | year | product | sales | |------|---------|-------| | 2020 | A | 100 | | 2020 | B | 200 | | 2021 | A | 150 | | 2021 | B | 300 | ### **实现方法** ✅ **方法 1：使用 `UNION ALL`** ```sql SELECT year, 'A' AS product, A AS sales FROM sales_data UNION ALL SELECT year, 'B' AS product, B AS sales FROM sales_data ORDER BY year, product; ``` **解释** - **第一个 `SELECT`** 提取 `A` 列，并将 `A` 作为 `product` 值。 - **第二个 `SELECT`** 提取 `B` 列，并将 `B` 作为 `product` 值。 - **`UNION ALL`** 合并两组查询结果，形成 `product` 列。 ## **3. 进阶方式** ### **（1）使用 `GROUP_CONCAT()` 处理多列行转列** 如果 `product` 可能有多个值（如 `A、B、C、D`），可用 **动态 SQL** 生成 `CASE WHEN` 语句： ```sql SELECT year, GROUP_CONCAT(DISTINCT CONCAT( 'SUM(CASE WHEN product = ''', product, ''' THEN sales ELSE 0 END) AS ', product ) ) INTO @sql FROM sales; SET @sql = CONCAT('SELECT year, ', @sql, ' FROM sales GROUP BY year ORDER BY year'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; ``` **优点** - 自动处理不同 `product` 值，而不用手动写 `CASE WHEN`。 ## **4. 总结** ### **✅ 行转列** | **方法** | **适用场景** | **优点** | **缺点** | |---------|------------|--------|--------| | `CASE WHEN` + `SUM()` | **字段值较少，列名固定** | 简单高效 | 需要手写字段 | | `GROUP_CONCAT()` 生成动态 SQL | **字段值较多，列名不固定** | 适应不同数据 | 需要动态 SQL | ### **✅ 列转行** | **方法** | **适用场景** | **优点** | **缺点** | |---------|------------|--------|--------| | `UNION ALL` | **列数较少，列名固定** | 简单高效 | 需要手写 SQL | | `UNPIVOT`（仅限 SQL Server） | **动态列转换** | 灵活 | MySQL 不支持 | 📌 **最佳实践** - **固定字段值（如 A、B）** → **`CASE WHEN` + `SUM()`** - **字段值不固定（如动态 product）** → **`GROUP_CONCAT()` 生成 SQL** - **列数少，转换行数有限** → **`UNION ALL`**