MySQL列转行的方法|极客笔记

MySQL列转行的方法

在数据库开发中，经常会遇到需要将列转行的情况，即将一张表中的多个列，转换成以某一列为依据的多行数据。在MySQL中，我们可以通过使用一些技巧和函数来实现列转行的操作。本文将详细介绍如何使用MySQL实现列转行的方法。

原始表结构

首先，让我们先来看一个需要进行列转行操作的表的结构。假设我们有一个student表，结构如下：

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    subject1 INT,
    subject2 INT,
    subject3 INT
);

INSERT INTO student (id, name, subject1, subject2, subject3)
VALUES 
(1, 'Alice', 75, 80, 90),
(2, 'Bob', 85, 89, 92),
(3, 'Carol', 92, 95, 88);

该表记录了学生的基本信息以及三门科目的成绩。我们需要将这个表中的subject1、subject2、subject3三个列转化为行数据。

使用UNION ALL实现列转行

一种简单粗暴的方法是使用UNION ALL关键字来将多个查询结果合并成一个结果集。具体做法是对每一列进行查询，然后通过UNION ALL将它们合并在一起。

SELECT id, name, 'subject1' AS subject, subject1 AS score FROM student
UNION ALL
SELECT id, name, 'subject2' AS subject, subject2 AS score FROM student
UNION ALL
SELECT id, name, 'subject3' AS subject, subject3 AS score FROM student
ORDER BY id, subject;

上面的SQL查询会将student表中的subject1、subject2、subject3三列转换为行数据，并按照学生ID和科目名称排序。运行结果如下：

+----+-------+----------+-------+
| id | name  | subject  | score |
+----+-------+----------+-------+
|  1 | Alice | subject1 |    75 |
|  1 | Alice | subject2 |    80 |
|  1 | Alice | subject3 |    90 |
|  2 | Bob   | subject1 |    85 |
|  2 | Bob   | subject2 |    89 |
|  2 | Bob   | subject3 |    92 |
|  3 | Carol | subject1 |    92 |
|  3 | Carol | subject2 |    95 |
|  3 | Carol | subject3 |    88 |
+----+-------+----------+-------+

使用UNPIVOT函数实现列转行

事实上，MySQL并没有提供类似于Oracle的UNPIVOT函数来实现列转行。但我们可以通过多次使用SELECT子查询来模拟实现UNPIVOT的功能。

SELECT id, name, 'subject1' AS subject, subject1 AS score FROM student
UNION ALL
SELECT id, name, 'subject2' AS subject, subject2 AS score FROM student
UNION ALL
SELECT id, name, 'subject3' AS subject, subject3 AS score FROM student
ORDER BY id, subject;

上面的SQL语句会将student表中的subject1、subject2、subject3三列转化为行数据，并按照学生ID和科目名称排序。结果与上一种方法相同。

使用CROSS JOIN和CASE语句实现列转行

另一种实现列转行的方法是使用CROSS JOIN和CASE语句。具体做法是将每个列的值通过CROSS JOIN进行排列组合，然后通过CASE语句选择出需要的值。

SELECT id,
       name,
       CASE subject_num
           WHEN 1 THEN 'subject1'
           WHEN 2 THEN 'subject2'
           WHEN 3 THEN 'subject3'
       END AS subject,
       CASE subject_num
           WHEN 1 THEN subject1
           WHEN 2 THEN subject2
           WHEN 3 THEN subject3
       END AS score
FROM student
CROSS JOIN (SELECT 1 AS subject_num
            UNION SELECT 2
            UNION SELECT 3) AS sub;

上面的SQL语句中，首先通过CROSS JOIN将学生表和子查询结果进行排列组合，然后通过CASE语句选择出相应的科目和分数。运行结果与之前的方法相同。