sql包含逗号的字段 统计数量

说明：统计psyt类型的rw数量，psyt存储的格式（11，22，33，44）

原理：
　　　　substring_index(a.usualusecurrentstatusid, ',', n)   ----截取第n个逗号前的数字

　　　　substring_index(a.usualusecurrentstatusid, ',', -1)   ----截取最后一个逗号后边的值

　　　　substring_index(substring_index(a.usualusecurrentstatusid, ',', b.id + 1), ',', - 1)

根据 后边的legth减法计算有几个数值，根据此数值循环拿出（11，22，33，44）最里层的截取是获得前面的数据，外层的截取是获得最后的数据
例：有四个值，因为on 条件是<4;
第一步 substring_index(a.usualusecurrentstatusid, ',', 1), ',', - 1) ------》11---》11

第二步substring_index(a.usualusecurrentstatusid, ',', 2), ',', - 1) ------》11，22---》22

第三步substring_index(a.usualusecurrentstatusid, ',', 3), ',', - 1) ------》11，22，33---》33

第四步substring_index(a.usualusecurrentstatusid, ',', 4), ',', - 1) ------》11，22，33，44---》44

最后根据group by 统计数量

select count(aa.bh) gs,sd.id rfxzId,sd.item_text rfxz,sum(aa.ryarea) z_ryarea,sum(aa.rfarea) z_rfarea  from (
SELECT a.bh,a.ryarea,a.rfarea
, substring_index(
substring_index(a.usualusecurrentstatusid, ',', b.id + 1), ',', - 1) AS usualusecurrentstatusid
FROM rf_projectinfo a
join sys_dict_item sdi on sdi.id=a.streetid and a.del=0

INNER JOIN rf_split_helper b
ON b.id  < (length(a.usualusecurrentstatusid) - length(REPLACE(a.usualusecurrentstatusid, ',', ''))
+ 1) where a.del=0) aa left join sys_dict_item sd on sd.id=aa.usualusecurrentstatusid

group by aa.usualusecurrentstatusid ORDER BY gs desc

需要增加辅助表：


create table rf_split_helper
        (
        id int(30) null
        );

        INSERT INTO rf_split_helper (id) VALUES (0);
        INSERT INTO rf_split_helper (id) VALUES (1);
        INSERT INTO rf_split_helper (id) VALUES (2);
        INSERT INTO rf_split_helper (id) VALUES (3);
        INSERT INTO rf_split_helper (id) VALUES (4);
        INSERT INTO rf_split_helper (id) VALUES (5);
        INSERT INTO rf_split_helper (id) VALUES (6);
        INSERT INTO rf_split_helper (id) VALUES (7);
        INSERT INTO rf_split_helper (id) VALUES (8);
        INSERT INTO rf_split_helper (id) VALUES (9);
        INSERT INTO rf_split_helper (id) VALUES (10);
        INSERT INTO rf_split_helper (id) VALUES (11);
        INSERT INTO rf_split_helper (id) VALUES (12);
        INSERT INTO rf_split_helper (id) VALUES (13);
        INSERT INTO rf_split_helper (id) VALUES (14);
        INSERT INTO rf_split_helper (id) VALUES (15);
        INSERT INTO rf_split_helper (id) VALUES (16);
        INSERT INTO rf_split_helper (id) VALUES (17);
        INSERT INTO rf_split_helper (id) VALUES (18);
        INSERT INTO rf_split_helper (id) VALUES (19);
        INSERT INTO rf_split_helper (id) VALUES (20);
        INSERT INTO rf_split_helper (id) VALUES (21);
        INSERT INTO rf_split_helper (id) VALUES (22);
        INSERT INTO rf_split_helper (id) VALUES (23);
        INSERT INTO rf_split_helper (id) VALUES (24);
        INSERT INTO rf_split_helper (id) VALUES (25);
        INSERT INTO rf_split_helper (id) VALUES (26);
        INSERT INTO rf_split_helper (id) VALUES (27);
        INSERT INTO rf_split_helper (id) VALUES (28);
        INSERT INTO rf_split_helper (id) VALUES (29);
        INSERT INTO rf_split_helper (id) VALUES (30);