mysql列转行sql|极客笔记

mysql列转行sql

在实际的数据处理中，有时候我们需要将一列数据转换为多行数据。在MySQL中可以通过一些简单的SQL语句来实现这一功能，称之为列转行。

实现列转行的方法

方法一：使用UNION ALL

使用UNION ALL可以将多个SELECT语句的结果合并为一个结果集。我们可以通过多次查询同一张表，每次查询获取一列数据，并使用UNION ALL将结果合并为多行数据。

例如，我们有一个表t_user，其中有三列数据，我们希望将这三列数据转换为一列数据：

SELECT name AS col_value FROM t_user
UNION ALL
SELECT age AS col_value FROM t_user
UNION ALL
SELECT gender AS col_value FROM t_user;

以上SQL语句将name、age、gender三列数据转换为一列数据，每个值对应一行。

方法二：使用UNION ALL和CROSS JOIN

在上面的方法中，我们需要多次查询同一张表，有时候可能效率较低。我们可以通过一次查询，并使用交叉连接CROSS JOIN的方式，来实现列转行。

例如，我们有一张表t_user，有三列数据，我们希望将这三列数据转换为一列数据：

SELECT col_value
FROM (
    SELECT name AS col_value, 1 AS ord FROM t_user
    UNION ALL
    SELECT age AS col_value, 2 AS ord FROM t_user
    UNION ALL
    SELECT gender AS col_value, 3 AS ord FROM t_user
) t
ORDER BY ord;

以上SQL语句通过一次查询和CROSS JOIN的方式将name、age、gender三列数据转换为一列数据，效率更高。

案例

假设我们有一张表t_student，结构如下：

CREATE TABLE t_student (
    id INT,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10)
);

INSERT INTO t_student (id, name, age, gender) VALUES (1, 'Alice', 18, 'female');
INSERT INTO t_student (id, name, age, gender) VALUES (2, 'Bob', 20, 'male');
INSERT INTO t_student (id, name, age, gender) VALUES (3, 'Cathy', 22, 'female');

我们希望将t_student表中的name、age、gender三列数据转换为一列数据，可以通过以下SQL语句实现：

SELECT col_value
FROM (
    SELECT name AS col_value, 1 AS ord FROM t_student
    UNION ALL
    SELECT age AS col_value, 2 AS ord FROM t_student
    UNION ALL
    SELECT gender AS col_value, 3 AS ord FROM t_student
) t
ORDER BY ord;

运行以上SQL语句，可以得到如下结果：

+-----------+
| col_value |
+-----------+
| Alice     |
| Bob       |
| Cathy     |
| 18        |
| 20        |
| 22        |
| female    |
| male      |
| female    |
+-----------+

通过以上案例，可以看到name、age、gender三列数据已经转换为了一列数据。