一、概述
MySQL 列转行函数（Pivot 转换或数据透视）是将列中的数据转换成行的形式，可以在大量数据分析中用于数据聚合。在 MySQL 中，有三种方法可以实现列转行功能：
- 使用 CASE WHEN 语句
- 使用 GROUP_CONCAT 函数
- 使用 UNION ALL 语句
本文将详细介绍这三种方法的原理和实现步骤。
二、使用 CASE WHEN 语句
使用 CASE WHEN 语句是实现列转行功能最常见的方法。其原理是将每个需要转置的列都分别用 CASE WHEN 语句进行处理，将所有处理后的结果用 UNION ALL 连接起来。
下面是一个例子：
SELECT id,
MAX(CASE WHEN subject = 'math' THEN score ELSE NULL END) AS math,
MAX(CASE WHEN subject = 'english' THEN score ELSE NULL END) AS english,
MAX(CASE WHEN subject = 'history' THEN score ELSE NULL END) AS history
FROM scores
GROUP BY id;
以上代码将 scores 表中的 id、subject 和 score 三个字段进行列转行，其结果将会是以下形式：
+------+----------+---------+---------+ | id | math | english | history | +------+----------+---------+---------+ | 1 | 80 | 90 | 85 | | 2 | 75 | 85 | 90 | | 3 | 95 | 92 | 80 | +------+----------+---------+---------+
以上代码使用了 MAX 函数，因为涉及到多个 CASE WHEN 语句，每个语句只有一条记录会生效，其他记录为 NULL。因此，可以使用 MAX 函数来将有效记录取出。
三、使用 GROUP_CONCAT 函数
GROUP_CONCAT 函数是用于将多行记录转换成单行记录字符串的函数。使用该函数实现列转行功能的原理是将每个需要转置的数据分别使用聚合函数和 GROUP BY 语句进行处理，并将结果通过 GROUP_CONCAT 函数连接起来。
下面是一个例子：
SELECT id,
GROUP_CONCAT(CASE WHEN subject = 'math' THEN score ELSE NULL END) AS math,
GROUP_CONCAT(CASE WHEN subject = 'english' THEN score ELSE NULL END) AS english,
GROUP_CONCAT(CASE WHEN subject = 'history' THEN score ELSE NULL END) AS history
FROM scores
GROUP BY id;
以上代码得到的结果与使用 CASE WHEN 语句的结果相同。但是，使用 GROUP_CONCAT 函数时需要注意被连接的字符串可能会超出 MySQL 默认配置的长度限制，因此需要事先进行调整。
四、使用 UNION ALL 语句
使用 UNION ALL 语句实现列转行功能的原理是将所有需要转置的列都分别作为子查询，并在最外层使用 UNION ALL 连接起来。
下面是一个例子：
SELECT id, 'math' AS subject, math AS score FROM scores UNION ALL SELECT id, 'english' AS subject, english AS score FROM scores UNION ALL SELECT id, 'history' AS subject, history AS score FROM scores ORDER BY id, subject;
以上代码得到的结果与使用前两种方法的结果相同。但是，由于需要多次查询，因此性能相对较差，适用于数据量比较小的情况。
五、注意事项
在使用列转行函数时，需要注意以下几点：
- 需要将转置后的数据进行聚合，因此需要使用 GROUP BY 语句。
- 需要处理的字段需要在 GROUP BY 语句中出现。
- 使用 CASE WHEN 语句时，需要每个需要转置的字段都使用一个 CASE WHEN 语句进行处理。
- 使用 GROUP_CONCAT 函数时，需要将需要连接的字符串进行长度控制。
六、总结
MySQL 列转行函数是一种将列数据转换成行数据的常用方法。在 MySQL 中，主要有三种方法可以实现列转行功能：使用 CASE WHEN 语句、使用 GROUP_CONCAT 函数和使用 UNION ALL 连接多个子查询。使用列转行函数时，需要注意需要转换的字段出现在 GROUP BY 语句中，同时使用 MAX 函数或 GROUP_CONCAT 函数选择要取出的有效记录。为了避免字符串长度超出 MySQL 默认配置的限制，使用 GROUP_CONCAT 函数时需要事先进行长度控制。
原创文章，作者：FTVH，如若转载，请注明出处：https://www.506064.com/n/136363.html
微信扫一扫 
支付宝扫一扫 