Mysql笔试题之单列拆分多列查询并合并显示

Mysql笔试题之单列拆分多列查询并合并显示

事情是这样的：某天在开发群里看同行们在聊天，有一位同行发了一个mysql笔试题。抱着试一试的心态研究了几个小时。话不多说直接上题。

题目

有学生表，结构和数据如下图：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `course` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

有科目表，结构和数据如下图：

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

要求如下图：

思路

看到这道题的时候首先想到了几个mysql函数GROUP_CONCAT、SEPARATOR 、CONCAT。好像是抓到了什么又好像无从下手。在网上扒拉了很久才解决了问题，因此这篇文章并非纯原创。

分析一下这个题：首先，student表中有三个字段id、name、course。course表中有两个字段id、name。其中student表中的course字段是合并了course表中id的字符串以“，”分割（表名已加粗）。最后，题目要求查询结果包含学生的id、名字、科目id加对应科目名。
分析完之后一个大概的sql框架已经出来了。

SELECT
	s.id,
	s.NAME,
	GROUP_CONCAT(
	... ...
	) AS course
FROM
	student s
GROUP BY
	s.id

GROUP_CONCAT()的作用后续会说明。GROUP_CONCAT中包含的就是主要逻辑了，分割student 表中的course字段并对应course表中的id查出name合并为（course.id.course.name,course.id.course.name）也就是（1. 语文,2. 数学,3. 英语,4. 物理,5. 化学）这种形式。

来个二次分析，怎么才能搞出来这种形式的数据呢？
分割数据肯定要用到SUBSTRING_INDEX()，但是由于sql语句的限制我们没办法像代码中使用for循环来直接循环student表中的course字段。然后我就在网上扒拉了两个小时… …（此处两个省略号代表两个小时）就发现了新大陆。

select substring_index(substring_index('82,83,84,85,86',',',help_topic_id+1),',',-1) as Id
 
from mysql.help_topic
 
where help_topic_id<(length('82,83,84,85,86')-length(replace('82,83,84,85,86',',',''))+1);

结果是这这样的

原文地址在这里 https://blog.csdn.net/hfsaini/article/details/86477371

芜湖！这不正是我想要的吗！！！

过程

通过发现的新大陆mysql.help_topic配合SUBSTRING_INDEX()就完成了分割student表中的course字段并对应course查询出了name。离成功只差最后一步。

SELECT CONCAT(
	SUBSTRING_INDEX( SUBSTRING_INDEX( s.course, ',', b.help_topic_id + 1 ), ',',- 1 ),
	'. ',
	(
SELECT
	course.NAME 
FROM
	course 
WHERE
	course.id = SUBSTRING_INDEX( SUBSTRING_INDEX( s.course, ',', b.help_topic_id + 1 ), ',',- 1 ) 
	) 
	) 
	FROM
	student s
	JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( s.course ) - LENGTH( REPLACE ( s.course, ',', '' ) ) + 1 ) 

结果

过程到结果又花了我茫茫多的时间 … …不过总算是弄出来了。

SELECT
	s.id,
	s.NAME,
	GROUP_CONCAT(
	CONCAT(
	SUBSTRING_INDEX( SUBSTRING_INDEX( s.course, ',', b.help_topic_id + 1 ), ',',- 1 ),
	'. ',
	(
SELECT
	course.NAME 
FROM
	course 
WHERE
	course.id = SUBSTRING_INDEX( SUBSTRING_INDEX( s.course, ',', b.help_topic_id + 1 ), ',',- 1 ) 
	) 
	) SEPARATOR ',' 
	) AS course 
FROM
	student s
	JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( s.course ) - LENGTH( REPLACE ( s.course, ',', '' ) ) + 1 ) 
GROUP BY
	s.id
	

函数说明

这里做一个简单的函数说明

• SUBSTRING_INDEX SUBSTRING_INDEX (str,delim,count)
  str:要处理的字符串
  delim:分隔符
  count:计数

  select SUBSTRING_INDEX("1,2,3,4,5",",",1)
  

   结果为1。如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。
  

• GROUP_CONCAT（），SEPARATOR

  GROUP_CONCAT SEPARATOR 可将查询结果用字符串连接变为一行,需配合使用 GROUP BY

  举个栗子：查询打分.打分是两个评委给的分数,每个人的成绩是有两个,但希望查出来的结果是用字符串连接的,变为一行数据

  SELECT
  	USERID,     		
  	SCORE                                       						
  FROM
  	TBL_SCORE
  

  

  SELECT
  	GROUP_CONCAT(USERID order by USERID separator ';') userId,     		
  	SCORE                                       						
  FROM
  	TBL_SCORE
  GROUP BY userId	
  

  

  举例原文地址 https://blog.csdn.net/linlinlinfeng/article/details/85045557

---

希望文章能够帮助到大家，二牛与大家共同进步！