当前位置：首页 > 行业动态 > 正文

如何实现MySQL中的行转列与列转行操作？

admin
行业动态
2024-12-05
2522

MySQL中行转列和列转行是数据透视操作，用于改变数据的显示方式。

在MySQL中，行转列和列转行是数据查询和分析中常见的操作，这些操作能够将数据以不同的方式展示，使得数据分析更加直观和高效，下面详细介绍这两种操作的实现方法和应用场景。

如何实现MySQL中的行转列与列转行操作？第1张

一、行转列（Pivot Table）

行转列是将一行数据记录转换为多列数据展示，通常用于根据某一列的值进行数据汇总，将学生的成绩按科目分别展示在不同的列中。

1. 使用CASE语句实现行转列

CREATE TABLE st_grade (
    id INT(10) NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(20) DEFAULT NULL,
    course VARCHAR(20) DEFAULT NULL,
    score FLOAT(4,1) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO st_grade (stu_name, course, score) VALUES ('张三', '语文', 86.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('张三', '数学', 90.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('张三', '英语', 75.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('李四', '语文', 92.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('李四', '数学', 93.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('李四', '英语', 96.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('王五', '语文', 82.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('王五', '数学', 71.0);
INSERT INTO st_grade (stu_name, course, score) VALUES ('王五', '英语', 74.0);
SELECT stu_name,
       MAX(CASE WHEN course = '语文' THEN score ELSE 0 END) AS '语文',
       MAX(CASE WHEN course = '数学' THEN score ELSE 0 END) AS '数学',
       MAX(CASE WHEN course = '英语' THEN score ELSE 0 END) AS '英语'
FROM st_grade
GROUP BY stu_name;

2. 使用IF函数实现行转列

SELECT stu_name,
       SUM(IF(course = '语文', score, 0)) AS '语文',
       SUM(IF(course = '数学', score, 0)) AS '数学',
       SUM(IF(course = '英语', score, 0)) AS '英语'
FROM st_grade
GROUP BY stu_name;

3. 使用PIVOT函数（MySQL 8.0+）

SELECT stu_name,
       JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.语文')) AS '语文',
       JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.数学')) AS '数学',
       JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.英语')) AS '英语'
FROM (
    SELECT stu_name,
           JSON_OBJECT('语文', score) AS json_data
    FROM st_grade
    WHERE course = '语文'
    UNION ALL
    SELECT stu_name,
           JSON_OBJECT('数学', score) AS json_data
    FROM st_grade
    WHERE course = '数学'
    UNION ALL
    SELECT stu_name,
           JSON_OBJECT('英语', score) AS json_data
    FROM st_grade
    WHERE course = '英语'
) AS subquery;

二、列转行（Unpivot Table）

列转行是将一列数据转换为多行数据记录展示，通常用于将某一列的不同值分散到多行中，将多个科目的成绩分别展示在多行中。

1. 使用UNION ALL实现列转行

SELECT stu_name, '语文' AS course, cn_score AS score FROM st_grade UNION ALL
SELECT stu_name, '数学' AS course, math_score AS score FROM st_grade UNION ALL
SELECT stu_name, '英语' AS course, en_score AS score FROM st_grade;

2. 使用UNPIVOT函数（MySQL 8.0+）

SELECT stu_name, course, score
FROM st_grade
UNPIVOT (score FOR course IN (语文, 数学, 英语));

三、常见问题解答（FAQs）

Q1: 如何在MySQL中实现动态行转列？

A1: 在MySQL中，可以使用PREPARE和EXECUTE动态生成SQL语句来实现动态行转列，具体步骤如下：

1、获取列名：通过查询获取需要转换的列名。

2、构建SQL语句：使用这些列名构建动态的SQL语句。

3、执行SQL语句：执行生成的SQL语句。

示例如下：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN course = ''',
      course,
      ''' THEN score ELSE 0 END) AS ',
      course
    )
  ) INTO @sql
FROM st_grade;
SET @sql = CONCAT('SELECT stu_name, ', @sql, ' FROM st_grade GROUP BY stu_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Q2: 如何在MySQL中实现动态列转行？

A2: 在MySQL中，可以使用PREPARE和EXECUTE动态生成SQL语句来实现动态列转行，具体步骤如下：

1、获取列名和对应的值：通过查询获取需要转换的列名和对应的值。

2、构建SQL语句：使用这些列名和值构建动态的SQL语句。

3、执行SQL语句：执行生成的SQL语句。

示例如下：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      "'",
      stu_name, "' AS stu_name, '",
      course, "' AS course, ",
      score, " AS score"
    ) SEPARATOR ' UNION ALL ' INTO @sql
FROM st_grade;
SET @sql = CONCAT('SELECT * FROM (', @sql, ') AS temp');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;