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函数，我们就完成了上面的问题。

先这样，大家先理解下，欢迎反馈。

BI、数据分析

MySQL-每周练习答案(2017-10-20)

测试数据

解决方案

SUBSTRING_INDEX

列转行