MySQL 列转行技巧与实例分享

在数据库处理中，我们常常会遇到需要将列数据转换为行数据的情况。这种列转行的操作能够让数据呈现和处理更加灵活高效。下面就为大家分享一些 MySQL 列转行的实用技巧及相关实例。

使用 UNION ALL 实现列转行是一种较为常见的方法。假设有一张成绩表 score_table，记录了学生不同科目的成绩，列名分别为 student_id、math_score、english_score、chinese_score。如果我们想将科目和成绩以行的形式展示，即一个学生的每科成绩都单独占一行。可以使用如下 SQL 语句：

SELECT student_id, 'Math' AS subject, math_score AS score
FROM score_table
UNION ALL
SELECT student_id, 'English' AS subject, english_score AS score
FROM score_table
UNION ALL
SELECT student_id, 'Chinese' AS subject, chinese_score AS score
FROM score_table;

这里通过 UNION ALL 将不同列的数据组合成新的行，每一部分都选择了相同的 student_id，然后分别用字符串表示科目，并选择相应的成绩列作为分数。

另一种方法是利用 MySQL 的函数和临时表来实现。例如使用 GROUP_CONCAT 函数结合临时表。还是以成绩表为例，先创建一个临时表存储转换后的数据：

CREATE TEMPORARY TABLE temp_score (
    student_id INT,
    subject VARCHAR(20),
    score INT
);

然后使用 INSERT INTO 语句结合 GROUP_CONCAT 函数填充临时表：

INSERT INTO temp_score (student_id, subject, score)
SELECT student_id, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', numbers.n), ',', -1) AS subject,
       SUBSTRING_INDEX(SUBSTRING_INDEX(scores, ',', numbers.n), ',', -1) AS score
FROM (
         SELECT student_id,
                GROUP_CONCAT('Math' ORDER BY student_id) AS subjects,
                GROUP_CONCAT(math_score ORDER BY student_id) AS scores
         FROM score_table
         UNION ALL
         SELECT student_id,
                GROUP_CONCAT('English' ORDER BY student_id) AS subjects,
                GROUP_CONCAT(english_score ORDER BY student_id) AS scores
         FROM score_table
         UNION ALL
         SELECT student_id,
                GROUP_CONCAT('Chinese' ORDER BY student_id) AS subjects,
                GROUP_CONCAT(chinese_score ORDER BY student_id) AS scores
         FROM score_table
     ) AS sub
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
ON CHAR_LENGTH(subjects) - CHAR_LENGTH(REPLACE(subjects, ',', '')) >= numbers.n - 1;

最后从临时表中查询结果：

SELECT * FROM temp_score;

通过这些技巧和实例，我们在面对 MySQL 列转行的需求时，能够更加从容应对，根据实际情况选择合适的方法来优化数据处理流程。