本文欢迎转载，转载请注明出处，谢谢~(作者：喝酒不骑马 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'