SQL列转行|极客教程

SQL列转行

在实际的数据处理中，我们经常会遇到将表中的列转换为行的需求。这种操作在SQL语言中被称为列转行，常用于数据透视表的生成、数据清洗、数据展示等场景。在本文中，我们将探讨如何使用SQL语言来实现列转行的操作，并给出一些实际的示例。

基本概念

在数据库表中，通常情况下每一列代表一个字段（或特征），每一行代表一个记录（或实例）。当我们需要将列转行时，实际上是将一列中的多个值拆分成多行，每行只包含一个值。这种操作在数据处理过程中十分常见，尤其在数据分析、报表生成、数据清洗等方面。

实现方法

使用UNION ALL

最简单的实现列转行的方法是使用UNION ALL操作符。UNION ALL用于汇总两个或多个SELECT语句的结果集，并保留所有的重复行。因此，我们可以通过多次查询同一张表，并将结果集通过UNION ALL合并来实现列转行的效果。

下面以一个示例表为例，展示使用UNION ALL实现列转行的方法：

CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    sub1 INT,
    sub2 INT,
    sub3 INT
);

INSERT INTO student (id, name, sub1, sub2, sub3)
VALUES
    (1, 'Alice', 80, 85, 90),
    (2, 'Bob', 75, 78, 80),
    (3, 'Cathy', 90, 92, 95);

SELECT id, name, 'sub1' AS subject, sub1 AS score FROM student
UNION ALL
SELECT id, name, 'sub2' AS subject, sub2 AS score FROM student
UNION ALL
SELECT id, name, 'sub3' AS subject, sub3 AS score FROM student;

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

 id | name  | subject | score
----+-------+---------+-------
  1 | Alice | sub1    |    80
  2 | Bob   | sub1    |    75
  3 | Cathy | sub1    |    90
  1 | Alice | sub2    |    85
  2 | Bob   | sub2    |    78
  3 | Cathy | sub2    |    92
  1 | Alice | sub3    |    90
  2 | Bob   | sub3    |    80
  3 | Cathy | sub3    |    95

从以上结果可以看出，通过使用UNION ALL操作符，我们成功将表中的三个科目（sub1, sub2, sub3）转换为了行，并同时保留了学生的id和name信息。

使用CROSS JOIN

除了使用UNION ALL，我们还可以借助CROSS JOIN操作符实现列转行的效果。CROSS JOIN用于生成两个表之间的笛卡尔积（即所有可能的组合）。通过将表自身与一个包含科目字段的虚拟表进行CROSS JOIN操作，我们可以实现列转行的效果。

下面是使用CROSS JOIN实现列转行的示例：

SELECT id, name,
       CASE WHEN subject = 'sub1' THEN sub1
            WHEN subject = 'sub2' THEN sub2
            WHEN subject = 'sub3' THEN sub3
       END AS score
FROM student
CROSS JOIN (VALUES ('sub1'), ('sub2'), ('sub3')) AS subjects(subject);

运行以上SQL语句，得到的结果与使用UNION ALL方法相同。在该示例中，我们通过CROSS JOIN将原始表与包含所有科目的虚拟表连接，然后通过CASE WHEN语句根据科目选择对应的分数值。