场景一:表中的某个列是一个存放人员编号的,一个列存了多个编号,用逗号隔开了,查询时需要关联查出每个人员姓名,如下图所示
图中的CYR字段就是人员编号
SQL:
select (
SELECT group_concat(u.USER_NAME SEPARATOR ',') from sys_uicm_user u
where u.ID_NUMBER in
(
select
substring_index(
substring_index(t1.CYR,",",(b.help_topic_id + 1)) ,",",- (1)
)
FROM ep_base_jx_harvest t1
INNER JOIN mysql.help_topic b ON b.help_topic_id < ((length(t1.CYR) - length(REPLACE(t1.CYR, ',', ''))) + 1)
WHERE t1.RESOURCE_ID = p.RESOURCE_ID
)
)
FROM ep_base_jx_harvest p
结果:
CYR_NAME
张三,李四,王五,王强,张磊,李国庆,廖文青,武松,杨飞虎
李白,王维,白居易,徐志华,百灵,牡丹,牡丹1,牡丹3,牡丹2
赵云
徐志华,马天宇,曾国华
姓名2,姓名24,姓名23,姓名32,姓名22,姓名1
sql中的函数:
group_concat:group_concat一般与SEPARATOR group_concat(查询的字段 SEPARATOR ',') 就是把查询的字段用","隔开
substring_index:substring_index('待截取的字符串','截取的依据字符','第几个依据字符N')
取第一个逗号前的字符串 :
SELECT SUBSTRING_INDEX('15,151,152,16',',',1); ==>得到结果为: 15
N可以为负数,表示倒数第N个索引字符后面的字符串。
(有负号的时候,可以将整个字符倒过来看,依旧是第N个字符前面的部分。)
SELECT SUBSTRING_INDEX('15,151,152,16',',',-1);==>得到结果为: 16
取第2个逗号前那部分字符串里,最后逗号后面的部分
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',2),',',-1);==>得到结果为: 151
取倒数第二个逗号后面部分字符串,再去这部分里第一个都号前的部分:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',-2),',',1);==> 得到结果为:152
SELECT SUBSTRING_INDEX('15,151,152,16',',',-1);==>得到结果为: 16
例如有一行的CYR有100个逗号分割的值,那么我们的incre_table就需要有至少100个连续行。
,mysql内部也有现成的连续数列表可用。如mysql.help_topic: help_topic_id 共有504个数值,一般能满足于大部分需求了