MySQL行转列详解|极客笔记

MySQL行转列详解

在MySQL数据库中，使用行转列操作可以将原本以行为单位存储的数据转化为以列为单位存储的形式，从而方便数据的分析和处理。本文将详细介绍MySQL中行转列的概念、方法以及常见的实例应用。

一、行转列概述

行转列是一种数据重塑技术，它可以将具有相同ID的行数据按照某一列的值进行汇总，将其转换为多列。行转列操作在实际应用中具有广泛的用途，比如将一张订单表中每个用户的每个订单以列的方式展示，方便分析用户的消费情况。

行转列操作可以使用多种方式实现，包括MySQL原生函数、CASE WHEN语句以及自连接等。下面将分别介绍这些方法的使用方式及示例。

二、MySQL原生函数- GROUP_CONCAT()

MySQL提供了GROUP_CONCAT()函数用于将聚合后的多个结果合并为一个字符串，常用于行转列操作的实现。

1. 示例代码

SELECT 
    user_id, 
    GROUP_CONCAT(order_id) AS orders
FROM 
    orders
GROUP BY 
    user_id;

2. 运行结果

user_id	orders
1	1001,1002
2	1003
3	1004,1005

三、CASE WHEN语句- 行转列

在MySQL中，可以使用CASE WHEN语句实现行转列操作。通过根据某一列的值对其他列进行分组，然后使用CASE WHEN语句将分组后的数据展示在不同的列中。

1. 示例代码

SELECT 
    user_id, 
    MAX(CASE WHEN order_id = 1001 THEN 'Yes' ELSE 'No' END) AS 'order_1001',
    MAX(CASE WHEN order_id = 1002 THEN 'Yes' ELSE 'No' END) AS 'order_1002',
    MAX(CASE WHEN order_id = 1003 THEN 'Yes' ELSE 'No' END) AS 'order_1003'
FROM 
    orders
GROUP BY 
    user_id;

2. 运行结果

user_id	order_1001	order_1002	order_1003
1	Yes	Yes	No
2	No	No	Yes
3	No	No	No

四、自连接- 行转列

另一种行转列的方式是通过自连接实现。自连接是指将同一张表视为多个不同的表进行连接操作。通过自连接，我们可以将原表中相同ID的行数据合并为一行，将其转换为多列。

1. 示例代码

SELECT 
    o1.user_id,
    o1.order_id,
    o2.order_id
FROM
    orders o1
LEFT JOIN
    orders o2
ON
    o1.user_id = o2.user_id
WHERE
    o1.order_id = 1001
    AND o2.order_id = 1002;

2. 运行结果

user_id	order_id	order_id
1	1001	1002

五、实例应用- 行转列

下面将通过一个实际的应用场景，演示如何使用行转列操作来处理数据。

场景描述：

有一张学生考试成绩表格，其中记录了学生的姓名和各科目的成绩。现在需要将该表格进行行转列操作，将每个学生的各科目成绩转换为不同的列。

1. 示例代码

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

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

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