MySQL
每周练习答案
这里和大家分享下本周练习题的一种解题思路
将题目简化一下，其实就是实现这样一个功能：
我们将使用逗号分隔的数据，拆分为多行数据，熟悉MySQL的同学，可能会想到，这有点儿像group_concat函数，但这是他的逆过程
测试数据
create table tm_company(
company_name varchar(10),
company_industry varchar(20)
);
insert into tm_company(company_name,company_industry) values('A公司','移动互联网,金融');
insert into tm_company(company_name,company_industry) values('B公司','移动互联网');
insert into tm_company(company_name,company_industry) values('C公司','教育,招聘,魔法');
select *from tm_company;
解决方案
我们先来思考这样一个问题，怎样才能把用逗号分隔的数据拆分呢？
这里需要了解MySQL的字符串函数
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
SUBSTRING_INDEX
找一下，会发现这个函数，挺符合我们需求的
SUBSTRING_INDEX(str,delim,count)
str 就是我们要分隔的字符串
delim 就是我们的分隔符
当count为正数时，我们从左侧开始截取，截止到delim第count次出现时；当count为负数时，则从右侧开始
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
我们来试一下
select
company_name,
substring_index(company_industry,',',1),
substring_index(company_industry,',',2),
substring_index(company_industry,',',-1)
from
tm_company;
看上去，好像接近我们想要的数据，但还是有些问题
比如，我们需要判断到底要分隔几次，那该怎样判断要分隔几次呢？
其实就是看有几个逗号，那怎样判断有几个逗号呢？
好像并没有判断字符出现次数的函数，但我们可以绕个弯来实现
像这样，我们把逗号都替换掉，看数据长度减少了多少，就可以了
select
company_name,
company_industry,
length(company_industry)-length(replace(company_industry,',',''))
from
tm_company;
我们再来观察下这个数据
- 1个逗号的时候，我们需要拆分2次
- 0个逗号的时候，我们需要拆分1次
- 2个逗号的时候，我们需要拆分3次
我们以C公司的数据来测试下
select
substring_index('教育,招聘,魔法',',',1),
substring_index('教育,招聘,魔法',',',2),
substring_index('教育,招聘,魔法',',',3)
我们观察下，会发现，最右边的数据就是我们想要的
select
substring_index(substring_index('教育,招聘,魔法',',',1),',',-1),
substring_index(substring_index('教育,招聘,魔法',',',2),',',-1),
substring_index(substring_index('教育,招聘,魔法',',',3),',',-1)
哎，好像是了嘛，数据现在已经分隔好了，现在的问题，好像变成了列转行
列转行
因为我们的数据是动态分隔的，所以，做列转行的话，不能用之前的方法，可能需要使用另一种方法
我们看下上面分隔的时候，使用的下标，是自增的，而且和逗号的数量也有关
畅想一下（估计是经验），可以这样来做
select *from (
select 0 as id
union select 1
union select 2
union select 3
union select 4
) base
我们可以这个数据来表示逗号的数量，然后这样
select
a.company_name,
a.company_industry,
substring_index(substring_index(a.company_industry,',',b.id+1),',',-1)
from
tm_company a
join (
select 0 as id
union select 1
union select 2
union select 3
union select 4
) b on b.id <= length(a.company_industry)-length(replace(a.company_industry,',',''))
order by
a.company_name;
好了，完成，我们通过1对多的关系，进行了列转行，再配合substring_index函数，我们就完成了上面的问题。
先这样，大家先理解下，欢迎反馈。
