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脚本和预处理语句

-- 第一步执行
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;

第二步执行结果

完毕！！

案例二：成绩表统计类型

图1转换成图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结果

列转行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;

完毕！！