详解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列转行的方法，包括基础语法、动态列转行、多列转行和行转列。在实际开发中，可以根据不同的场景选择合适的方法来实现列转行，提高数据处理的效率。