什么是行轉(zhuǎn)列

行轉(zhuǎn)列指的是將數(shù)據(jù)庫(kù)表中的行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)的過程。例如，假設(shè)我們有如下的表結(jié)構(gòu)和數(shù)據(jù)：

CREATE TABLE Sales (
    Year INT,
    Quarter VARCHAR(10),
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (Year, Quarter, Amount) VALUES
(2023, 'Q1', 1000.00),
(2023, 'Q2', 1500.00),
(2023, 'Q3', 1200.00),
(2023, 'Q4', 1800.00);

表 Sales 存儲(chǔ)了不同季度的銷售額。如果我們希望將這些數(shù)據(jù)轉(zhuǎn)換為按季度展開的形式，使每個(gè)季度的數(shù)據(jù)都作為單獨(dú)的一列顯示，則需要進(jìn)行行轉(zhuǎn)列操作。目標(biāo)表結(jié)構(gòu)如下：

行轉(zhuǎn)列的常見方法

在 MySQL 中，行轉(zhuǎn)列操作可以通過以下幾種方法實(shí)現(xiàn)：

1. 使用條件聚合（CASE WHEN + 聚合函數(shù)）

這種方法是最常見且最兼容的方式，通過CASE WHEN語(yǔ)句和聚合函數(shù)（如SUM、MAX等）來(lái)實(shí)現(xiàn)行轉(zhuǎn)列。

示例：

SELECT
    Year,
    SUM(CASE WHEN Quarter = 'Q1' THEN Amount ELSE 0 END) AS Q1,
    SUM(CASE WHEN Quarter = 'Q2' THEN Amount ELSE 0 END) AS Q2,
    SUM(CASE WHEN Quarter = 'Q3' THEN Amount ELSE 0 END) AS Q3,
    SUM(CASE WHEN Quarter = 'Q4' THEN Amount ELSE 0 END) AS Q4
FROM
    Sales
GROUP BY
    Year;

適用場(chǎng)景： 條件聚合方法適用于需要對(duì)不同條件的數(shù)據(jù)進(jìn)行聚合的場(chǎng)景，適用所有 SQL 數(shù)據(jù)庫(kù)。

2. 使用動(dòng)態(tài) SQL 生成行轉(zhuǎn)列查詢

對(duì)于列的數(shù)量不確定或在運(yùn)行時(shí)動(dòng)態(tài)生成時(shí)，可以使用動(dòng)態(tài) SQL 來(lái)生成行轉(zhuǎn)列查詢。

示例：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN Quarter = ''',
      Quarter,
      ''' THEN Amount ELSE 0 END) AS `',
      Quarter, '`'
    )
  ) INTO @sql
FROM Sales;

SET @sql = CONCAT('SELECT Year, ', @sql, ' FROM Sales GROUP BY Year');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

適用場(chǎng)景： 動(dòng)態(tài) SQL 適用于列名不固定，或需要?jiǎng)討B(tài)生成列名的場(chǎng)景。注意，這種方法較復(fù)雜，且 SQL 注入風(fēng)險(xiǎn)需要特別注意。

3. 使用 IF 函數(shù)

類似于CASE WHEN，IF函數(shù)可以用于簡(jiǎn)單的行轉(zhuǎn)列操作。

示例：

SELECT
    Year,
    SUM(IF(Quarter = 'Q1', Amount, 0)) AS Q1,
    SUM(IF(Quarter = 'Q2', Amount, 0)) AS Q2,
    SUM(IF(Quarter = 'Q3', Amount, 0)) AS Q3,
    SUM(IF(Quarter = 'Q4', Amount, 0)) AS Q4
FROM
    Sales
GROUP BY
    Year;

適用場(chǎng)景： IF函數(shù)語(yǔ)法較簡(jiǎn)潔，適用于條件較少且不復(fù)雜的行轉(zhuǎn)列操作。

4. 使用 GROUP_CONCAT 與 SUBSTRING_INDEX（簡(jiǎn)單字符串拼接方式）

對(duì)于某些簡(jiǎn)單的場(chǎng)景，可以通過GROUP_CONCAT與SUBSTRING_INDEX函數(shù)將多行拼接成一列，但這種方法更適合小規(guī)模數(shù)據(jù)且無(wú)法嚴(yán)格控制數(shù)據(jù)類型。

示例：

SELECT
    Year,
    GROUP_CONCAT(Quarter, ':', Amount ORDER BY Quarter ASC SEPARATOR ', ') AS QuarterAmounts
FROM
    Sales
GROUP BY
    Year;

結(jié)果示例：

適用場(chǎng)景： 適用于快速將多行內(nèi)容拼接為一列字符串展示的場(chǎng)景，但不適合嚴(yán)格的數(shù)據(jù)分析任務(wù)。

5. 使用 JOIN 自連接

在行轉(zhuǎn)列時(shí)，也可以通過多次自連接來(lái)實(shí)現(xiàn)，但這種方法只適用于行轉(zhuǎn)列項(xiàng)較少的情況。

示例：

SELECT
    t1.Year,
    t1.Amount AS Q1,
    t2.Amount AS Q2,
    t3.Amount AS Q3,
    t4.Amount AS Q4
FROM
    Sales t1
    LEFT JOIN Sales t2 ON t1.Year = t2.Year AND t2.Quarter = 'Q2'
    LEFT JOIN Sales t3 ON t1.Year = t3.Year AND t3.Quarter = 'Q3'
    LEFT JOIN Sales t4 ON t1.Year = t4.Year AND t4.Quarter = 'Q4'
WHERE
    t1.Quarter = 'Q1';

適用場(chǎng)景： 自連接適用于列轉(zhuǎn)換的數(shù)量不多且數(shù)據(jù)規(guī)模較小時(shí)。隨著列數(shù)的增加，這種方法的查詢復(fù)雜度和效率問題也會(huì)增加。

6. 使用PIVOT關(guān)鍵字

在 SQL Server 中，可以使用 PIVOT 關(guān)鍵字來(lái)簡(jiǎn)化行轉(zhuǎn)列操作。

語(yǔ)法結(jié)構(gòu)：

SELECT 
    Year, 
    [Q1], 
    [Q2], 
    [Q3], 
    [Q4]
FROM 
    (SELECT Year, Quarter, Amount FROM Sales) AS SourceTable
PIVOT 
    (MAX(Amount) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) AS PivotTable;

PIVOT 是一種更加直觀的方式，特別是在需要轉(zhuǎn)列的字段較多時(shí)，可以大大簡(jiǎn)化查詢語(yǔ)句。

結(jié)語(yǔ)

行轉(zhuǎn)列是 SQL 中一項(xiàng)非常有用的技能，它能夠幫助我們以更加直觀的方式展示和分析數(shù)據(jù)。通過上述方法，您可以根據(jù)自己的需求靈活地進(jìn)行行轉(zhuǎn)列操作。當(dāng)然，在實(shí)際應(yīng)用中，還需要考慮性能和維護(hù)性等因素。