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

时间:2023-01-03 10:01:16

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


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

_id addresss
1 7,77,893

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

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

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

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

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

现在的需求是,要把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'

实际效果如下
MySQL种字符串拆分(substring_index)并作为关联查询条件后多列展示
需求完美解决!

其中,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'