本文共 3553 字，大约阅读时间需要 11 分钟。

行转列与列转行——数据库数据转换方法

在数据库处理中，行转列和列转行是常见的数据转换需求。本文将详细介绍几种常用方法，帮助开发者高效完成数据转换任务。

一、行转列

行转列的核心思想是将一个表中同一字段的多行数据转化为多个字段。以下是实现行转列的具体方法：

1. 使用CASE语句

CASE语句结合SUM函数，可以实现行转列。例如：

SELECT userid,        SUM(CASE `subject` WHEN '语文' THEN score ELSE 0) AS 语文,       SUM(CASE `subject` WHEN '数学' THEN score ELSE 0) AS 数学,       SUM(CASE `subject` WHEN '英语' THEN score ELSE 0) AS 英语,       SUM(CASE `subject` WHEN '政治' THEN score ELSE 0) AS 政治FROM tb_scoreGROUP BY userid;

这种方法通过检查每个subject字段的值来累加对应的成绩。

2. 使用IF()函数

IF()函数也可以用来实现行转列。例如：

SELECT userid,        SUM(IF(`subject`='语文', score, 0)) AS 语文,       SUM(IF(`subject`='数学', score, 0)) AS 数学,       SUM(IF(`subject`='英语', score, 0)) AS 英语,       SUM(IF(`subject`='政治', score, 0)) AS 政治FROM tb_scoreGROUP BY userid;

IF()函数检查每个subject字段的值，如果匹配则累加对应的分数。

3. 使用SUM()和ROLLUP

结合SUM()和ROLLUP可以实现行转列，并生成总计行。例如：

SELECT IFNULL(userid, 'total') AS userid,       SUM(IF(`subject`='语文', score, 0)) AS 语文,       SUM(IF(`subject`='数学', score, 0)) AS 数学,       SUM(IF(`subject`='英语', score, 0)) AS 英语,       SUM(IF(`subject`='政治', score, 0)) AS 政治,       SUM(score) AS totalFROM (    SELECT userid, `subject`, SUM(score) AS score    FROM tb_score    GROUP BY userid, `subject`    WITH ROLLUP    HAVING userid IS NOT NULL) AS AGROUP BY useridWITH ROLLUP;

这种方法通过子查询和ROLLUP生成总计行，同时使用IFNULL处理默认值。

4. 使用UNION和GROUP BY

UNION和GROUP BY结合可以实现行转列。例如：

SELECT userid, `subject`, scoreFROM tb_scoreGROUP BY useridWITH ROLLUP;SELECT 'total', `subject`, SUM(score)FROM tb_scoreGROUP BY `subject`WITH ROLLUP;

这种方法先按userid分组，再按subject分组，最后生成总计行。

5. 动态字段处理

当字段数量不确定时，可以使用动态SQL生成转换结果。例如：

SET @EE = '';SELECT @EE := CONCAT(@EE, 'SUM(IF(subject='', score, 0)) AS ', subject, ',') AS aaFROM (SELECT DISTINCT subject FROM tb_score) A;SET @QQ = CONCAT('SELECT IFNULL(userid, "total") AS userid, @EE, SUM(score) AS total FROM tb_score GROUP BY userid WITH ROLLUP');PREPARE stmt FROM @QQ;EXECUTE stmt;DEALLOCATE PREPARE stmt;

这种方法通过动态构建SQL语句，适用于字段数量不确定的情况。

6. 使用GROUP_CONCAT()

GROUP_CONCAT()函数可以将多个字段的值连接为一个字符串。例如：

SELECT userid, GROUP_CONCAT(`subject`, ':', score) AS 成绩FROM tb_scoreGROUP BY userid;

这种方法适合将多个字段的值以字符串形式展示。

二、列转行

列转行的目标是将一个表中多个字段的数据转化为多个记录。以下是实现列转行的方法：

1. 使用UNION ALL

UNION ALL可以将多个字段的数据合并为一个结果集。例如：

SELECT userid, cn_score AS 语文, math_score AS 数学, en_score AS 英语, po_score AS 政治FROM tb_score1WHERE userid = '001';SELECT userid, cn_score AS 语文, math_score AS 数学, en_score AS 英语, po_score AS 政治FROM tb_score1WHERE userid = '002';SELECT userid, cn_score AS 语文, math_score AS 数学, en_score AS 英语, po_score AS 政治FROM tb_score1WHERE userid = '003';UNION ALLSELECT 'TOTAL', SUM(cn_score) AS 语文, SUM(math_score) AS 数学, SUM(en_score) AS 英语, SUM(po_score) AS 政治, SUM(po_score) AS 总分FROM tb_score1;

这种方法通过UNION ALL将多个字段的数据合并为一个结果集。

2. 使用GROUP BY和CASE

结合GROUP BY和CASE可以实现列转行。例如：

SELECT userid,        CASE WHEN `subject` = '语文' THEN cn_score ELSE 0 END AS 语文分数,       CASE WHEN `subject` = '数学' THEN math_score ELSE 0 END AS 数学分数,       CASE WHEN `subject` = '英语' THEN en_score ELSE 0 END AS 英语分数,       CASE WHEN `subject` = '政治' THEN po_score ELSE 0 END AS 政治分数FROM tb_score1GROUP BY userid;

这种方法通过检查每个subject字段的值来选择对应的分数。

3. 使用Prepared Statement

动态构建SQL语句可以处理字段数量不确定的情况。例如：

SET @subjects = '语文,数学,英语,政治';SELECT @subjects := CONCAT(@subjects, ',总分') AS columns;PREPARE stmt FROM 'SELECT userid, `subject`, score FROM tb_score1 WHERE `subject` IN ('@subjects')';EXECUTE stmt;DEALLOCATE PREPARE stmt;

这种方法适合动态处理字段数量不确定的情况。

总结

行转列和列转行是数据库处理中的常见需求。选择合适的方法根据具体场景进行优化。无论是使用CASE、IF()、UNION还是动态SQL，理解数据库的GROUP BY、ROLLUP等函数是关键。在实际应用中，根据数据结构和查询需求，合理选择转换方式可以显著提升效率。

转载地址：http://hbbfk.baihongyu.com/