MySQL种字符串拆分(substring_index)并作为关联查询条件后多列展示

本文欢迎转载，转载请注明出处，谢谢~(作者：喝酒不骑马 Colton_Null)
from CSDN

---

最近的项目中有个需求。有关用户的地址信息是存在person_info_t表中的address字段中，省市区用ID存放，中间用逗号隔开。如下表所示

_id	addresss
1	7,77,893

address中的数字是城市信息表(region表)中的id。如下图所示

现在的需求是，要把7,77,893拆开，分别用7,77,893去region表中查询对应省市区的名称并用多列形式展示。例如：

_id	province	city	district
1	辽宁省	锦州市	凌河区

查询了各种资料，果然高手在民间啊。整理了可以实现上述方法的SQL语句如下：

select pi._id ,
(select re.REGION_NAME from region re inner join person_info_t pi on re.REGION_ID = SUBSTRING_INDEX(pi.address,',',1)) as province,
(select re.REGION_NAME from region re inner join person_info_t pi on re.REGION_ID = SUBSTRING_INDEX(SUBSTRING_INDEX(pi.address,',',2),',',-1)) as city,
(select re.REGION_NAME from region re inner join person_info_t pi on re.REGION_ID = SUBSTRING_INDEX(pi.address,',',-1)) as district
from person_info_t pi where pi._id = '1'

实际效果如下

需求完美解决！

其中，SUBSTRING_INDEX(str,delim,count)函数是用来分割字符串的。
说明：SUBSTRING_INDEX（被截取字段，关键字，关键字出现的次数）
例：select SUBSTRING_INDEX（"www.aimaonline.cn"，"."，2） as test from code_t
结果：www.aimaonline
如果关键字出现的次数是负数，如-2，则是从后倒数，到字符串结束，对应结果即为aimaonline.cn

上述SQL还有一种实现方法

select pi._id,  
(select re.REGION_NAME from region re , person_info_t pi where re.REGION_ID = SUBSTRING_INDEX(pi.address,',',1)) as province,
(select re.REGION_NAME from region re , person_info_t pi where re.REGION_ID = SUBSTRING_INDEX(SUBSTRING_INDEX(pi.address,',',2),',',-1)) as city,
(select re.REGION_NAME from region re , person_info_t pi where re.REGION_ID = SUBSTRING_INDEX(pi.address,',',-1)) as district
from person_info_t pi where pi._id = '1'