行列互转、行拆分与合并简介

什么是行列互转呢？简单理解就是行转列和列转行，横向变竖向，竖向转换到横向。那行拆分与合并又是什么呢？行合并是将多行合并为一行，一对一的关系，行拆分就是将一行拆分为多行，一对多的关系。可能这样大家理解起来比较枯燥，下面利用案例来进行实操。

1、行列互转

-- 创建表create table student_score ( name varchar(10), english int, maths int, music int);-- 插入数据insert into student_score values("张三",90,88,64),("李四",57,76,99)

-- 列转行select name,'english' as subject,english as score from student_score unionselect name,'maths' as subject,maths as score from student_score unionselect name,'music' as subject,music as score from student_score

-- 行转列select name, max(case when subject="english" then score end) as english, max(case when subject="maths" then score end) as maths, max(case when subject="music" then score end) as musicfrom(select name,'english' as subject,english as score from student_score unionselect name,'maths' as subject,maths as score from student_score unionselect name,'music' as subject,music as score from student_score)agroup by name

2、行拆分与合并

/*将成绩进行行合并，指定分隔符为","，也可以是其他分隔符例如："|",";"等，这里使用","*/select name, concat_ws(',',english,maths,music) as score, concat_ws(',',"english","maths","music") as subjectfrom student_score

/*行拆分这里利用到了排序，借助的是mysql数据库中的help_topic表的id，这里的id是从0开始编号的。通过计算score的长度与替换分隔符以后的score的长度做差+1，通过条件，可得到每行记录编号，然后根据substring_index函数通过分隔符进行索引截取*/
SELECT a.name, SUBSTRING_INDEX( SUBSTRING_INDEX( a.score, ',', h.help_topic_id + 1 ), ',',- 1 ) AS score, SUBSTRING_INDEX( SUBSTRING_INDEX( a.subject, ',', h.help_topic_id + 1 ), ',',- 1 ) AS subjectFROM(select name, concat_ws(',',english,maths,music) as score, concat_ws(',',"english","maths","music") as subjectfrom student_score)a JOIN mysql.help_topic h ON h.help_topic_id < ( ( length( a.score ) - length( REPLACE ( a.score, ',', '' ) ) + 1 ) )

/*行合并这里通过group_concat函数，并且指定分隔符，即可分隔符语法：separator ','，这里我使用 ','，你可以使用其他分隔符：例如："|",";"等*/
with combine as (SELECT a.name, SUBSTRING_INDEX( SUBSTRING_INDEX( a.score, ',', h.help_topic_id + 1 ), ',',- 1 ) AS score, SUBSTRING_INDEX( SUBSTRING_INDEX( a.subject, ',', h.help_topic_id + 1 ), ',',- 1 ) AS subjectFROM(select name, concat_ws(',',english,maths,music) as score, concat_ws(',',"english","maths","music") as subjectfrom student_score)a JOIN mysql.help_topic h ON h.help_topic_id < ( ( length( a.score ) - length( REPLACE ( a.score, ',', '' ) ) + 1 ) ))
select name, group_concat(score separator ',') as score, group_concat(subject separator ',') as subjectfrom combinegroup by name

最后

本文使用了max，concat_ws，group_concat等函数以及条件表达式的应用，实现了行列互转、行拆分与列合并，对于比较基础的知识点我们要加以熟练掌握！（注：排序的很重要，很重要，很重要！！！）