mysql 列转成逗号隔开的字符串

面试中，我们经常遇见行转列和列转行的题目，针对于这类题目我们做一些总结：

1.行转列

题目1：柠檬班第30期学生要毕业了，他们的Linux、MySQL、Java成绩数据表 tb_lemon_grade_column中， 表中字段student_name，Linux，MySQL，Java分别表示学生姓名、Linux成绩、MySQL成绩、Java成绩， 数据图1所示。请写出一条SQL，将图1的数据变成图2的形式，如下图:

细看这个问题，就知道是需要将图1中的一行转成图二中的一列，并且需要生成自增的id 

 我们先看Mysql的解法：

一种比较容易想到的方案就是，先查询所有的Linux，MySQL，Java 组织成图二的格式，如下SQL

 

SELECT student_name , Linux  AS '课程成绩' , 'Linux' AS '课程名称' FROM tb_lemon_grade_column AS a

SELECT student_name , MySQL  AS '课程成绩' , 'MySQL' AS '课程名称' FROM tb_lemon_grade_column AS a 

SELECT student_name , Java  AS '课程成绩' , 'Java' AS '课程名称' FROM tb_lemon_grade_column AS a

   用union all 将他们连接起来，如下：

 

SELECT student_name , Linux  AS '课程成绩' , 'Linux' AS '课程名称' FROM tb_lemon_grade_column AS a
 UNION All
SELECT student_name , MySQL  AS '课程成绩' , 'MySQL' AS '课程名称' FROM tb_lemon_grade_column AS a 
 UNION All
SELECT student_name , Java  AS '课程成绩' , 'Java' AS '课程名称' FROM tb_lemon_grade_column AS a

结果入下图：

 

然后再对这个结果排序并生成自增id

如下SQL：

SELECT  aaa.成绩id,aaa.学生姓名,aaa.课程名称,aaa.课程成绩 FROM (
SELECT  @id AS '成绩id',aa.student_name AS '学生姓名',aa.课程名称,aa.课程成绩,@id:=@id+1  FROM 
(SELECT student_name , Linux  AS '课程成绩' , 'Linux' AS '课程名称' FROM tb_lemon_grade_column AS a
 UNION ALL
SELECT student_name , MySQL  AS '课程成绩' , 'MySQL' AS '课程名称' FROM tb_lemon_grade_column AS a 
 UNION ALL
SELECT student_name , Java  AS '课程成绩' , 'Java' AS '课程名称' FROM tb_lemon_grade_column AS a) AS aa,(SELECT @id:=1) AS bb
ORDER BY aa.student_name) AS aaa

结果如下：

    

Hive的解法：

  hive的解法和Mysql基本一致，就是生成的自然主键的方式有所不同，如下SQL:

SELECT  aaa.成绩id,aaa.学生姓名,aaa.课程名称,aaa.课程成绩 FROM (
SELECT  row_number()over() AS row_number '成绩id',aa.student_name AS '学生姓名',aa.课程名称,aa.课程成绩,@id:=@id+1  FROM 
(SELECT student_name , Linux  AS '课程成绩' , 'Linux' AS '课程名称' FROM tb_lemon_grade_column AS a
 UNION
SELECT student_name , MySQL  AS '课程成绩' , 'MySQL' AS '课程名称' FROM tb_lemon_grade_column AS a 
 UNION
SELECT student_name , Java  AS '课程成绩' , 'Java' AS '课程名称' FROM tb_lemon_grade_column AS a) AS aa
ORDER BY aa.student_name) AS aaa

题目2：有如下格式数据

    

学生id	学生姓名	课程
1	张宇祺	数学，语文，英语
2	陈晓峰	数学，语文
3	刘能	历史，语文
4	赵四	语文，自然

通过SQL实现如下：

 

学生id	学生姓名	课程
1	张宇祺	数学
1	张宇祺	语文
1	张宇祺	英语
2	陈晓峰	数学
2	陈晓峰	语文
3	刘能	历史
3	刘能	语文
4	赵四	语文
4	赵四	自然

 我们先看Mysql的解法：

    通过观察，我们发现需要对课程进行切割，mysql中的切割函数有

            1、left(str,index) 从左边第index开始截取

           2、right(str,index)从右边第index开始截取

           3、substring(str,index)当index>0从左边开始截取直到结束  当index<0从右边开始截取直到结束  当index=0返回空

           4、substring(str,index,len) 截取str,从index开始，截取len长度

           5、substring_index(str,delim,count)，str是要截取的字符串，delim是截取的字段 count是从哪里开始截取(为0则是左边第0个开始，1位左边开始第一个选取左边的，-1从右边第一个开始选取右边的

我们选用第5个函数可以实现我们的字符串增量截取，如下：

 SUBSTRING_INDEX(SUBSTRING_INDEX(a.course, ',',1),',',-1)  表示截取第一个字符，以逗号分隔后，同理 SUBSTRING_INDEX(SUBSTRING_INDEX(a.course, ',',2),',',-1)  截取第二个，由此可知，第二个 SUBSTRING_INDEX的count 只要是个增量序列（0,1,2,3）就可以实现这个需求了，

     实现这种增量序列其实很简单，我们可以自己创建一个id从0开始自增的，也可以使用mysql提供的工具表：mysql.`help_topic`  结构如下：

其中help_topic_id就是我们需要的，如下sql：

SELECT
 a.user_id,
 a.user_name,
 SUBSTRING_INDEX(SUBSTRING_INDEX(a.course, ',',c.help_topic_id+1),',',-1) AS tag
FROM
 student_course AS a LEFT JOIN mysql.`help_topic` AS c ON c.help_topic_id  <=   LENGTH(a.course) - LENGTH(REPLACE(a.course,',',''))

其中LENGTH(a.course) - LENGTH(REPLACE(a.course,',','')) 是计算分隔符的个数，通过 c.help_topic_id  <=   LENGTH(a.course) - LENGTH(REPLACE(a.course,',',''))的条件，实现自增，结果如下：

 Hive的解法：

   对于hive来说，实现方式就简单多了，我们知道hive中提供了UDTF（操作一个数据行，产生多个数据行一个表作为输出） ：

我们先通过split函数将course转换成array类型，如下：

select split(a.course,',')  from student_course as a;

结果如下：

 

然后用explode函数转成多行：

select explode(split(a.course,','))  from student_course as a;

结果如下：

explode 函数有个问题，就是无法和表中其他字段联合使用，也就是说，如下SQL会出现问题：

select a.user_id,  explode(split(a.course,','))  from student_course as a;

报错如下：

Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

所以我们需要借助 lateral view，下面是 lateral view 的作用介绍

select o.*, table_view.new_col
from table_origin o
lateral view UDTF(expression) table_view as `new_col_1`, `new_col_2`

  

• lateral view 表示将UDTF分裂的字段放在虚拟表中, 然后和主表table_origin进行关联。
• UDTF(expression)：复合逻辑规则的UDTF函数，最常用的explode
• table_view : 对应的虚拟表的表名
• new_col: 虚拟表里存放的有效字段
• from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了

利用lateral view 产生如下SQL:

SELECT o.*, table_view.new_col_1
FROM student_course o
lateral VIEW explode(split(o.course,',')) table_view AS `new_col_1`

结果如下：

1.列转行

 我们先看Mysql的解法：

题目3：我们针对题目一中的考题进行修改：如下图

如上图：我们已知图2，如何得到图l的组合呢，对于这种典型的列转行，首先我们想到的是利用case when进行判断，如下：

SELECT aa.`student_name`,
    CASE WHEN aa.course_name ='Linux'  THEN aa.course_score
         ELSE 0 END 'Linux',
    CASE WHEN aa.course_name ='MySQL'  THEN aa.course_score
         ELSE 0 END 'MySQL'  ,   
     CASE WHEN aa.course_name ='Java'  THEN aa.course_score
         ELSE 0 END 'Java'  
   FROM `opp_tb_lemon_grade_column` AS aa

结果如下：

我们看到，并没有达到我们想要的结果，会产生多个值，我们对其进行分组，取最大值

如下：

SELECT aa.`student_name`,
    MAX(CASE WHEN aa.course_name ='Linux'  THEN aa.course_score
         ELSE 0 END) 'Linux',
    MAX(CASE WHEN aa.course_name ='MySQL'  THEN aa.course_score
         ELSE 0 END) 'MySQL'  ,   
    MAX(CASE WHEN aa.course_name ='Java'  THEN aa.course_score
         ELSE 0 END) 'Java'
         
   FROM `opp_tb_lemon_grade_column` AS aa
 GROUP BY   aa.`student_name`

结果如下：

hive 的解法：

这个题hive的解法和mysql一致，如上图：

题目4：有入下格式的数据

     

1	a
1	b
2	c
2	d
3	e
3	f

 

期望得到如下结果的数据：

                                                   

1	a,b
2	c,d
3	e,f

表名是t_table,列分别 f_a1,f_a2

      Mysql实现方案如下：

SELECT a.`f_a1`,GROUP_CONCAT(a.`f_a2`) FROM t_table AS a  
 GROUP BY a.`f_a2`

   Hive实现方案如下：

 

select a.f_a1 ,  collect_list(a.f_a2) from `t_table` as a group by a.f_a1;