如何实现MySQL中的行列转换及行列存储？

MySQL行列转换是数据库操作中常见的需求，尤其在数据分析和报表生成时，本文将详细介绍如何在MySQL中实现行转列和列转行的转换方法，并结合具体示例进行说明。

一、行转列

行转列（Pivot）是将表中的行数据转换为列数据，将学生的成绩表从按科目存储转换为按学生存储。

示例数据：

目标结果：

实现方法：

1、使用CASE...WHEN...THEN 语句

SELECT userid,
       SUM(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS '语文',
       SUM(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS '数学',
       SUM(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS '英语'
FROM tb_score
GROUP BY userid;

2、使用IF() 函数

SELECT userid,
       SUM(IF(subject = '语文', score, 0)) AS '语文',
       SUM(IF(subject = '数学', score, 0)) AS '数学',
       SUM(IF(subject = '英语', score, 0)) AS '英语'
FROM tb_score
GROUP BY userid;

3、利用SUM(IF()) 生成列 +WITH 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 = 'total', score, 0)) AS 'total'
FROM (
    SELECT userid, IFNULL(subject, 'total') AS subject, SUM(score) AS score
    FROM tb_score
    GROUP BY userid, subject WITH ROLLUP
    HAVING userid IS NOT NULL
) AS A
GROUP BY userid WITH ROLLUP;

4、动态列名

如果列名不确定，可以使用动态SQL生成查询语句，这种方法较为复杂，但可以处理不确定数量的列。

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SUM(IF(subject = "', subject, '", score, 0)) AS ',
            subject
        )
    ) INTO @sql
FROM tb_score;
SET @sql = CONCAT('SELECT userid, ', @sql, ' FROM tb_score GROUP BY userid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

二、列转行

列转行（Unpivot）是将表中的列数据转换为行数据，将学生的成绩表从按学生存储转换为按科目存储。

示例数据：

目标结果：

实现方法：

1、使用UNION ALL

SELECT userid, '语文' AS subject, 语文 AS score FROM tb_score UNION ALL
SELECT userid, '数学' AS subject, 数学 AS score FROM tb_score UNION ALL
SELECT userid, '英语' AS subject, 英语 AS score FROM tb_score;

2、使用递归CTE

如果列的数量较多，可以使用递归CTE动态生成查询语句，这种方法较为复杂，但可以处理不确定数量的列。

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SELECT userid, '', column_name, '' AS subject, ', column_name, ' AS score FROM tb_score'
        ) INTO @sql
FROM information_schema.columns
WHERE table_name = 'tb_score' AND column_name != 'userid';
SET @sql = CONCAT(@sql, ' UNION ALL SELECT userid, NULL AS subject, NULL AS score FROM tb_score LIMIT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

三、FAQs

Q1: 为什么需要行转列或列转行？

A1: 行转列和列转行在数据分析和报表生成中非常常见，行转列可以将数据整理成更适合展示的格式，而列转行则可以将宽表转换为长表，便于数据的进一步处理和分析。

Q2: MySQL是否支持像SQL Server那样的PIVOT和UNPIVOT功能？

A2: MySQL本身不支持像SQL Server那样的PIVOT和UNPIVOT功能，但是可以通过上述提到的各种方法来实现类似的效果，使用CASE语句、IF函数、动态SQL等方式都可以实现行转列和列转行的操作。

小编有话说

MySQL中的行列转换虽然不像SQL Server那样有专门的函数支持，但通过灵活运用SQL语句和一些技巧，同样可以实现复杂的数据转换操作，掌握这些技巧不仅可以提高数据处理的效率，还能增强对数据库操作的理解和应用能力，在进行行列转换时，建议根据实际需求选择合适的方法，并在必要时进行性能优化。