相关链接
# step1 构建一个带从0开始自增主键的临时表
# 注意:字符串中分隔符数量 < 临时表t1数据条数 (不够就增加临时表数据量)
with t1 as (
select '0' as id union select '1' union
select '2' union select '3' union select '4' union
select '5' union select '6' union select '7' union
select '8' union select '9' union select '10' union
select '11' union select '12' union select '13' union
select '14' union select '15' union select '16' union
select '17' union select '18' union select '19')
SELECT
# step3 将step2分割的结果,再次反向切片,得到结果
SUBSTRING_INDEX(
# step2 用主键作为索引,正向对字符串切片
SUBSTRING_INDEX('apple,orange,bnana,
litchi',
',',
t1.id+1),
',',
-1
) AS result
FROM
t1
WHERE
t1.id <
LENGTH('apple,orange,bnana, litchi')-
LENGTH(REPLACE('apple,orange,bnana, litchi',',',''))+1
22/07/15
M