詳解mysql列轉行的方法

在數據處理時，我們經常需要將數據從列轉化成行，或者將數據從行轉化成列。本文將以mysql列轉行的方法為中心，從多個方面對此方法進行詳細闡述。

一、基礎語法

mysql列轉行可以通過UNION ALL語句來實現。UNION ALL可以將多個SELECT查詢的結果集合併成一個結果集，並且不去重。列轉行的思路就是將多個列合併成一行，然後使用UNION ALL連接多個結果集，最終實現列轉行。

--假設有如下表：
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(10) DEFAULT NULL,
  `col2` varchar(10) DEFAULT NULL,
  `col3` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

--查詢語句
SELECT 'col1' AS col_name, col1 AS col_value FROM test
UNION ALL
SELECT 'col2' AS col_name, col2 AS col_value FROM test
UNION ALL
SELECT 'col3' AS col_name, col3 AS col_value FROM test;

以上查詢語句的意思是，將表test中的三個列col1、col2、col3合併成一列，並加上列名col_name和列值col_value。執行結果如下：

+----------+-----------+
| col_name | col_value |
+----------+-----------+
| col1     | NULL      |
| col2     | NULL      |
| col3     | NULL      |
+----------+-----------+

由於表test中沒有數據，所以返回結果為空。但是，以上查詢語句的基本思路就是將多個列合併成一行，並在一行中加上列名和列值。

二、動態列轉行

有時候需要將動態列轉換成行，這是列轉行的難點之一。動態列是指表中的列名不確定，可能會隨時增加或減少。在這種情況下，我們需要使用動態sql語句來實現列轉行。

使用動態sql時，我們需要通過查詢information_schema來獲取表的列名，然後將列名動態拼接到查詢語句中。以下是動態列轉行的示例代碼：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN column_name = ''',
      column_name,
      ''' THEN column_value ELSE NULL END) AS `',
      column_name, '`'
    )
  ) INTO @sql
FROM
  information_schema.columns
WHERE
  table_name = 'mytable';

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                   FROM mytable
                   GROUP BY id');

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

以上代碼中，我們首先使用SELECT獲取表mytable的列名，然後使用GROUP_CONCAT動態拼接列轉行後的SQL，並將其存儲到變數@sql中。最後，使用PREPARE語句執行@sql中拼接的SQL查詢語句，並用EXECUTE語句進行執行。

三、多列轉行

有時候需要將多列轉換成行，這是列轉行的另一個難點。多列轉行的方法比較靈活，可以針對不同的場景採用不同的方法。

以下是將多列轉換成行的示例代碼：

--方法一：使用UNION ALL
SELECT 'col1' AS col_name, col1 AS col_value FROM mytable
UNION ALL
SELECT 'col2' AS col_name, col2 AS col_value FROM mytable
UNION ALL
SELECT 'col3' AS col_name, col3 AS col_value FROM mytable;

--方法二：使用UNION ALL和CASE WHEN
SELECT 'column1' AS col_name,
       CASE WHEN column_name = 'column1' THEN column_value ELSE NULL END AS col_value
FROM mytable
UNION ALL
SELECT 'column2' AS col_name,
       CASE WHEN column_name = 'column2' THEN column_value ELSE NULL END AS col_value
FROM mytable

--方法三：使用CASE WHEN和UNION ALL
SELECT 'column1' AS col_name,
       column_value AS col_value
FROM mytable
WHERE column_name='column1'
UNION ALL
SELECT 'column2' AS col_name,
       column_value AS col_value
FROM mytable
WHERE column_name='column2'

以上代碼中，方法一是最常用的方法，將每個列分別查詢出來，然後使用UNION ALL合併。方法二和方法三都使用了CASE WHEN語句，方法二在每個查詢語句中都使用了CASE WHEN，方法三則使用了WHERE子句和UNION ALL語句。

四、行轉列

行轉列是列轉行的逆操作，將多行轉換成一行。在mysql中，我們可以使用GROUP_CONCAT函數來實現行轉列。

SELECT id, GROUP_CONCAT(column_value SEPARATOR ',') AS all_columns
FROM mytable
GROUP BY id;

以上代碼中，使用GROUP_CONCAT函數將所有列值連接成一個字元串，並以逗號分隔。最後使用GROUP BY語句指定按id列分組，將多行轉換成一行。

五、總結

本文詳細闡述了mysql列轉行的方法，包括基礎語法、動態列轉行、多列轉行和行轉列。在實際開發中，可以根據不同的場景選擇合適的方法來實現列轉行，提高數據處理的效率。