一文弄懂MYSQL如何列转行

背景

在数据库中，有时候需要将列转换成行来展示数据。例如一个表中有多个日期字段，需要将每个日期字段的值作为新的行的一列来展示数据。

原理

MYSQL中提供了UNION ALL语句来实现列转行的功能。该语句可以将多个SELECT语句的结果合并成一个结果集。通过多个SELECT语句中的UNION ALL，可以将多行数据合并成一行，达到列转行的效果。

实现步骤

1. 编写多个SELECT语句，每个SELECT语句查询一个需要转换的数据列，并且每个SELECT语句查询的结果集必须是一样的，以便能够合并成一行。在每个SELECT语句中，需要增加一个常量列来标识当前选取的数据列的名称，以便能够在后续的操作中对数据进行区分。
2. 将多个SELECT语句合并成一个结果集，使用UNION ALL语句将多个SELECT语句的结果进行合并。需要注意的是，如果数据列的数据类型不一致，需要将数据类型转换成一致的类型。
3. 利用GROUP BY语句对结果集进行分组，按照常量列对结果集进行分组。这样可以将多行数据合并成一行，并且每列数据的值作为一个新列显示在查询结果中。

示例1

假设存在一张包含多个日期字段的表，需要将每个日期字段的值作为新的行的一列来展示，如下图所示：

id	date1	date2	date3
1	2022-01-01	2022-01-02	2022-01-03
2	2022-01-04	2022-01-05	2022-01-06
3	2022-01-07	2022-01-08	2022-01-09

可以使用以下SQL语句实现转换：

SELECT 'date1', date1 FROM table_name
UNION ALL
SELECT 'date2', date2 FROM table_name
UNION ALL
SELECT 'date3', date3 FROM table_name
GROUP BY 1;

执行以上SQL语句后，得到以下查询结果：

'date1'	'date2'	'date3'
date1	2022-01-01	2022-01-02
date2	2022-01-02	2022-01-03
date3	2022-01-03	2022-01-04
date1	2022-01-04	2022-01-05
date2	2022-01-05	2022-01-06
date3	2022-01-06	2022-01-07
date1	2022-01-07	2022-01-08
date2	2022-01-08	2022-01-09
date3	2022-01-09	NULL

查询结果中，每个日期字段的值都在一个新列中显示，每个新列的列名由常量列进行标识。

示例2

假设存在一张包含多个课程的成绩表，需要将每个课程的成绩作为新的行的一列来展示，如下图所示：

id	course1_grade	course2_grade	course3_grade
1	75	80	85
2	88	90	91
3	92	95	98

可以使用以下SQL语句实现转换：

SELECT 'course1_grade', course1_grade FROM table_name
UNION ALL
SELECT 'course2_grade', course2_grade FROM table_name
UNION ALL
SELECT 'course3_grade', course3_grade FROM table_name
GROUP BY 1;

执行以上SQL语句后，得到以下查询结果：

'course1_grade'	'course2_grade'	'course3_grade'
course1_grade	75	80
course2_grade	80	85
course3_grade	85	88
course1_grade	88	90
course2_grade	90	91
course3_grade	91	92
course1_grade	92	95
course2_grade	95	98
course3_grade	98	NULL

查询结果中，每个课程的成绩都在一个新列中显示，每个新列的列名由常量列进行标识。

总结

通过UNION ALL语句和GROUP BY语句可以实现MYSQL中的列转行功能。具体实现步骤包括编写多个SELECT语句、将多个SELECT语句合并成一个结果集、利用GROUP BY语句对结果集进行分组。事实上，通过类似的方法还可以实现其他数据转换功能。