sql之行转列和列转行

数据准备

CREATE TABLE `student_x` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (1, '张三', '数学', 78);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (2, '张三', '英语', 93);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (3, '张三', '语文', 65);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (4, '李四', '数学', 87);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (5, '李四', '英语', 90);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (6, '李四', '语文', 76);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (7, '李四', '历史', 69);

行转列

-- 方法1
select name,
	max(case when class = '数学' then score else null end) as math_score,
	max(case when class = '英语' then score else null end) as engilsh_score,
	max(case when class = '语文' then score else null end) as chinese_score,
	max(case when class = '历史' then score else null end) as history_score
from student_x
group by name;

-- 方法2
select name,
	max(if(class = '数学', score, null)) as math_score,
	max(if(class = '英语', score, null)) as engilsh_score,
	max(if(class = '语文', score, null)) as chinese_score,
	max(if(class = '历史', score, null)) as history_score
from student_x
group by name;

-- 方法3
set @select_columns = ''; 
select @select_columns := concat(@select_columns,'sum(if(class= \'',class,'\',score,null)) as ',class, ',') as select_column 
from (
	select distinct class from student_x -- 统计去重后的科目名称集合
) as t; 

set @select_sql := 
concat('select name, ', substring(@select_columns, 1, char_length(@select_columns) - 1),' from student_x group by name;'); 
 
prepare stmt from @select_sql; -- 准备执行SQL语句
execute stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放变量资源

结果图：

**

列转行

CREATE TABLE `student_y` (
  `id` int(11) DEFAULT NULL, 
  `name` varchar(255) DEFAULT NULL,
  `math_score` bigint(255) DEFAULT NULL,
  `engilsh_score` bigint(255) DEFAULT NULL,
  `chinese_score` bigint(255) DEFAULT NULL,
  `history_score` bigint(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (1, '张三', 78, 93, 65, NULL);
INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (2, '李四', 87, 90, 76, 69);

select * from (
	select name, 'math_score' as class, math_score as score from student_y
	union all
	select name, 'engilsh_score' as class, engilsh_score as score from student_y
	union all
	select name, 'chinese_score' as class, chinese_score as score from student_y
	union all
	select name, 'history_score' as class, history_score as score from student_y
) as x order by name,class;

CREATE TABLE `student_y2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `scores` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `student_y2`(`id`, `name`, `scores`) VALUES (1, '张三', '78,93,65');
INSERT INTO `student_y2`(`id`, `name`, `scores`) VALUES (2, '李四', '87,90,76,69');

select 
	x.name,
	x.scores,
	char_length(x.scores) as length_scores,
	char_length(replace(x.scores, ',', '')) as length_scores_with_out_comma,
	char_length(x.scores) - char_length(replace(x.scores, ',', '')) + 1 as colum_num,
	substring_index(x.scores, ',', 1) as score1,
	substring_index(x.scores, ',', 2) as score12,
	substring_index(x.scores, ',', 3) as score123,
	substring_index(x.scores, ',', 4) as score1234,
	substring_index(substring_index(x.scores, ',', 1), ',', -1) as colum1,
	substring_index(substring_index(x.scores, ',', 2), ',', -1) as colum2,
	substring_index(substring_index(x.scores, ',', 3), ',', -1) as colum3,
	substring_index(substring_index(x.scores, ',', 4), ',', -1) as colum4
from student_y2 as x;

select 
	x.name,
	substring_index(substring_index(x.scores, ',', y.help_topic_id + 1), ',', -1) as 'score'
from student_y2 as x
join mysql.help_topic y
on y.help_topic_id < (length(x.scores) - length(replace(x.scores, ',', '')) + 1);