蓝桉云顶

Good Luck To You!

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

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

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

一、行转列(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中,可以使用PREPAREEXECUTE动态生成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中,可以使用PREPAREEXECUTE动态生成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;

小编有话说

通过本文的介绍,相信大家对MySQL中的行转列和列转行操作有了更深入的理解,无论是使用CASE语句、IF函数还是PIVOT/UNPIVOT函数,都能有效地实现数据的转换和展示,在实际工作中,选择合适的方法可以大大提高数据处理的效率和准确性,希望本文能为大家的数据查询和分析工作带来帮助。

  •  心跳
     发布于 2024-03-06 21:35:06  回复该评论
  • 通过golang 接口 指针类型 值类型,深入理解Go语言中接口、指针和值类型的关联与区别,为实际编程提供有力支持。

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

«    2024年12月    »
1
2345678
9101112131415
16171819202122
23242526272829
3031
控制面板
您好,欢迎到访网站!
  查看权限
网站分类
搜索
最新留言
文章归档
网站收藏
友情链接