1. HTTP3W博客首页
  2. 技术
  3. 数据库

MySQL行转列与列传行

工作中有时会碰到SQL语句行转列或列转行问题,在不同需求场景下写法也有不同

我的MySQL服务器版本是5.6.26

案例一:不固定表头类型

第一步:设置Mysql group_concat长度

该示例中用的group_concat函数,因为group_concat长度默认设置为1024;而我们表头又很多时,所以我们需要提前解决Mysql group_concat长度限制问题,MySQL中执行如下语句:

SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

第二步:通过MySQL脚本

CREATE TABLE `point_tablevalue` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
DROP TABLE IF EXISTS `point_tablevalue`;
CREATE TABLE `point_tablevalue` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `column_value` bigint(128) NOT NULL COMMENT 'table列标识',
  `row_number` bigint(128) NOT NULL,
  `value` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'table列具体值',
  `table_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4933 DEFAULT CHARSET=utf8;

MySQL行转列与列传行
table_id为单个表条件查询

第三步:编写MySQL脚本和预处理语句

-- 第一步执行
set @sql=null;
-- 拼接
select   GROUP_CONCAT(DISTINCT CONCAT('GROUP_CONCAT(case column_value',' when ''',column_value,''' then value else null end) as ''',column_value,''''))  
into @sql from point_tablevalue;
-- 查看拼接结果
select @sql;

set @sql =CONCAT('select b.row_number,', @sql,'from point_tablevalue b  GROUP BY row_number');
SELECT @sql;

-- 使用预执行 PREPARE 声明 stmt(定义预处理语句)
PREPARE stmt FROM @sql;

-- 第二步执行
-- 开始执行预处理语句
EXECUTE stmt;

-- 清除
(此操作会清除上面两步过程,不必执行)
DEALLOCATE PREPARE stmt;

第二步执行结果

MySQL行转列与列传行

完毕!!

案例二:成绩表统计类型

图1转换成图2结果展示

MySQL行转列与列传行
图1
MySQL行转列与列传行
图2

建立脚本

CREATE TABLE `TEST_TB_GRADE` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

运行SQL

SELECT user_name ,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_tb_grade
GROUP BY USER_NAME;

列转行

将图1转图3结果

MySQL行转列与列传行
图3

列转行SQL

select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE;

完毕!!

1、原创文章,作者:诺米,如若转载,请注明出处:https://www.http3w.com/archives/354

2、本站内容若有雷同从属巧合,若侵犯了您的权益,请联系本站删除,E-mail: wtao219@qq.com

发表评论

您的电子邮箱地址不会被公开。

联系我们

254007489

在线咨询:点击这里给我发消息

邮件:wtao219@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息