SQL函数之分割

数据库中有一张地区数据统计表，但是并不规则 ，记录类似于，225100:02:3:20160725是一串代码，以：分割，第1位为地区代码，第2位为分类代码，第3位为数量，第4位为日期

地区代码含义225100-上海  225200-江苏  225300-浙江 为可能有某些位不存在，缺位时计算规则如下：

1、地区缺位时不参与统计

2、时间缺位时按20151220来计算

表里数据为：

 

最终要的效果为：

1 给225300:02:14:加上20151220

        select  id,'225300:02:14:20151220' as part from chq3g4 where id = 8

2 去掉:开头的也就是地区缺位，

        select id,part from chq3g4  where id != 4

3 将第一条数据用；劈开：

        select 11 as id,  substr(part, 0, instr(part, ';')) as part from chq3g4 where id=1
UNION  ALL
select 12 as id,  substr(part, instr(part, ';')+1) as part from chq3g4 where id=1

或者

        WITH split(word, str) AS (
    -- alternatively put your query here
    -- SELECT '', category||',' FROM categories
    SELECT '', part  ||';' from chq3g4 where id != 4
    UNION ALL SELECT
    substr(str, 0, instr(str, ';')),
    substr(str, instr(str, ';')+1)
    FROM split WHERE str!=''
) SELECT  word as part FROM split WHERE word!='';

4 最后全部加起来：

        select 11 as id,  substr(part, 0, instr(part, ';')) as part from chq3g4 where id=1
UNION  ALL
select 12 as id,  substr(part, instr(part, ';')+1) as part from chq3g4 where id=1
UNION  ALL
select id,part from chq3g4  where id != 4 and  id !=8 and id != 1
UNION  ALL 
select  id,'225300:02:14:20151220' as part from chq3g4 where id = 8