MySQL行列转换常见的操作方法详解

目录

• 前言
• 一、行转列（PIVOT）
• 示例场景：学生成绩表行转列
• 1. 创建原始表
• 2. 行转列实现方法
• 二、列转行（UNPIVOT）
• 示例场景：销售数据列转行
• 1. 创建原始表
• 2. 列转行实现方法
• 三、动态行列转换
• 四、性能优化建议
• 五、实际应用场景

前言

行列转换是SQL中常见的操作，主要包括两种类型：行转列（PIVOT）和列转行（UNPIVOT）。mysql虽然没有直接的PIVOT/UNPIVOT语法，但可以通过条件聚合和UNION ALL等方式实现。

一、行转列（PIVOT）

行转列是将行数据转换为列数据，通常用于将分类数据作为列名展示。

示例场景：学生成绩表行转列

1. 创建原始表

CREATE TABLE student_scores (
    student_id INT,
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INT
);

INSERT INTO student_scores VALUES
(1, '张三', '数学', 90),
(1, '张三', '语文', 85),
(1, '张三', '英语', 92),
(2, '李四', '数学', 88),
(2, '李四', '语文', 90),
(2, '李四', '英语', 87),
(3, '王五', '数学', 95),
(3, '王五', '语文', 78),
(3, '王五', '英语', 85);www.devze.com

2. 行转列实现方法

使用条件聚合函数：

SELECT 
    student_id,
    student_name,
    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS '数学',
    MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS '语文',
    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS '英语'
FROM student_scores
GROUP BY student_id, student_name;

结果：

+------------+--------------+--------+--------+--------+
| student_id | student_name | 数学   | 语文   | 英语   |
+------------+--------------+--------+--------+--------+
|          1 | 张三         |     90 |     85 |     92 |
|          2 | 李四         |     88 |     90 |     87 |
|          3 | 王五   www.devze.com      |     95 |     78 |     85 |
+------------+--------------+--------+--------+--------+

二、列转行（UNPIVOT）

列转行是将列数据转换为行数据，通常用于将多列数据转换为键值对形式。

示例场景：销售数据列转行

1. 创建原始表

CREATE TABLE sales_data (
    id INT,
    product_name VARCHAR(50),
    q1_sales INT,php
    q2_sales INT,
    q3_sales INT,
    q4_sales INT
);

INSERT INTO sales_data VALUES
(1, '产品A', 120, 150, 180, 200),
(2, '产品B', 90, 110, 130, 150),
(3, '产品C', 200, 220, 240, 260);

2. 列转行实现方法

使用UNION ALL：

SELECT 
    id,
    product_name,
    'Q1' AS quarter,
    q1_sales AS sales
FROM sales_data
UNION ALL
SELECT 
    id,
    product_name,
    'Q2' AS quarter,
    q2_sales AS sales
FROM sales_data
UNION ALL
SELECT 
    id,
    product_name,
    'Q3' AS quarter,
    q3_sales AS sales
FROM sales_data
UNION ALL
SELECT 
    id,
    product_name,
    'Q4' AS quarter,
    q4_sales AS sales
FROM sales_data
ORDER BY id, quarter;

结果：

+----+--------------+---------+-------+
| id | product_name | quarter | sales |
+----+--------------+---------+-------+
|  1 | 产品A        | Q1      |   120 |
|  1 | 产品A        | Q2      |   150 |
|  1 | 产品A        | Q3      |   180 |
|  1 | 产品A        | Q4      |   200 |
|  2 | 产品B        | Q1      php|    90 |
|  2 | 产品B        | Q2      |   110 |
|  2 | 产品B        | Q3      |   130 |
|  2 | 产品B        | Q4      |   150 |
|  3 | 产品C        | Q1      |   200 |
|  3 | 产品C        | Q2      |   220 |
|  3 | 产品C        | Q3      |   240 |
|  3 | 产品C        | Q4      |   260 |
+----+--------------+---------+-------+

三、动态行列转换

当列名不确定或经常变化时，可以使用动态SQL实现行列转换。

-- 创建存储过程实现动态行转列
DELIMITER //
CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(50);
    DECLARE col_list TEXT DEFAULT '';
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT subject FROM student_scores;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SEjavascriptT done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET col_list = CONCAT(col_list, 
                             IF(col_list = '', '', ', '), 
                             'MAX(CASE WHEN subject = ''', col_name, ''' THEN score ELSE NULL END) AS ''', col_name, '''');
    END LOOP;
    CLOSE cur;
    
    SET @sql = CONCAT('SELECT student_id, student_name, ', col_list, ' FROM student_scores GROUP BY student_id, student_name');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程
CALL dynamic_pivot();

四、性能优化建议

1. 对于大型数据集的列转行，考虑使用临时表替代UNION ALL
2. 在行转列时，确保GROUP BY子句包含所有非聚合列
3. 为常用转换创建视图，提高查询效率
4. 在动态SQL中，注意防止SQL注入

五、实际应用场景

1. 报表生成：将行数据转换为适合报表展示的列格式
2. 数据透视：分析不同维度的数据关系
3. ETL过程：数据清洗和转换
4. 数据展示：将数据库格式转换为前端需要的格式

到此这篇关于MySQL行列转换常见操作方法的文章就介绍到这了,更多相关MySQL行列转换内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！