SQL列转行|极客笔记

SQL列转行

在数据库中，我们常常会遇到一种需求：将一列数据转换成多行。这种操作在SQL中被称为列转行。它可以帮助我们更好地处理和分析数据。本文将详细介绍什么是列转行，以及如何在SQL中实现该功能。

1. 什么是列转行

列转行是将一列数据按照某种规则拆分成多行的操作。它通常用于将一些拥有相同属性的数据进行分解，以便更好地进行数据分析和处理。

举个示例，假设有以下表格：

CREATE TABLE scores (
    student_id INT,
    score1 INT,
    score2 INT,
    score3 INT
);

INSERT INTO scores VALUES
    (1, 90, 80, 70),
    (2, 85, 95, 75),
    (3, 92, 88, 94);

这个表格记录了学生的成绩，其中score1、score2和score3分别表示三门课的成绩。如果我们希望将这些成绩按照学生和科目进行展示，可以使用列转行的方法。

2. 列转行的实现方式

在SQL中，实现列转行通常有多种方式，包括使用UNION、CROSS JOIN和PIVOT等操作。下面将详细介绍这些方法的使用。

2.1 使用UNION实现列转行

使用UNION操作符可以将多个查询结果合并成一个结果集。我们可以利用这一特性实现列转行。

以下是使用UNION实现列转行的示例：

SELECT student_id, 'score1' AS subject, score1 AS score FROM scores
UNION ALL
SELECT student_id, 'score2' AS subject, score2 AS score FROM scores
UNION ALL
SELECT student_id, 'score3' AS subject, score3 AS score FROM scores;

结果：

student_id	subject	score
1	score1	90
2	score1	85
3	score1	92
1	score2	80
2	score2	95
3	score2	88
1	score3	70
2	score3	75
3	score3	94

通过使用UNION操作，我们将原先的一列数据转换成了三列，并按照学生和科目进行了展示。

2.2 使用CROSS JOIN实现列转行

另一种实现列转行的方式是使用CROSS JOIN操作符。CROSS JOIN可以对两个表进行笛卡尔积操作。当我们只有一个表时，可以用它进行列转行的操作。

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

SELECT
    student_id,
    CASE
        WHEN subject_id = 1 THEN 'score1'
        WHEN subject_id = 2 THEN 'score2'
        WHEN subject_id = 3 THEN 'score3'
    END AS subject,
    CASE
        WHEN subject_id = 1 THEN score1
        WHEN subject_id = 2 THEN score2
        WHEN subject_id = 3 THEN score3
    END AS score
FROM scores
CROSS JOIN (SELECT 1 AS subject_id UNION ALL SELECT 2 UNION ALL SELECT 3) subjects;

结果与使用UNION的方式相同：

student_id	subject	score
1	score1	90
2	score1	85
3	score1	92
1	score2	80
2	score2	95
3	score2	88
1	score3	70
2	score3	75
3	score3	94

2.3 使用PIVOT实现列转行

如果数据库支持PIVOT操作，那么可以使用它来实现列转行。PIVOT是一种将表中的行数据转换为列数据的操作，适用于某些特定情况下的列转行。

以下是使用PIVOT实现列转行的示例（仅适用于部分数据库）：

SELECT
    student_id,
    COLUMNS.subject,
    COLUMNS.score
FROM (
    SELECT 'score1' AS subject, score1 AS score FROM scores
    UNION ALL
    SELECT 'score2' AS subject, score2 AS score FROM scores
    UNION ALL
    SELECT 'score3' AS subject, score3 AS score FROM scores
) COLUMNS
PIVOT (
    MAX(score)
    FOR subject IN ('score1', 'score2', 'score3')
) PIVOTED;

结果与前面的示例相同。