少儿编程培训加盟品牌费用_屋顶平台设计效果图大全_如何做好seo基础优化_微信小程序官网

场景：
qc_basic_info 个人档案表存在一个POPULATION_TYPE字段，字段存的是0-9的人群分类数组字符串，因为有的存的是‘2，3’，有的存的是‘2,3,4’，需求是统计每个人群分类的人数，所以需要先把数组拆分出来再分组统计

				WITH split_population AS (SELECTt.rec_province_id,t.rec_city_id,t.rec_district_id,t.rec_town_id,t.rec_village_id,t.basic_id,t.is_delete,t.status,TRIM(REGEXP_SUBSTR( t.POPULATION_TYPE, '[^,]+', 1, LEVEL )) AS population_type FROMqc_basic_info t CONNECT BY PRIOR t.id = t.id AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL AND LEVEL <= LENGTH( t.POPULATION_TYPE ) - LENGTH(REPLACE ( t.POPULATION_TYPE, ',', '' )) + 1 ),filtered_data AS (SELECT* FROMsplit_population spLEFT JOIN sys_rec rec1 ON rec1.rec_code = sp.rec_province_idLEFT JOIN sys_rec rec2 ON rec2.rec_code = sp.rec_city_idLEFT JOIN sys_rec rec3 ON rec3.rec_code = sp.rec_district_idLEFT JOIN sys_rec rec4 ON rec4.rec_code = sp.rec_town_idLEFT JOIN sys_rec rec5 ON rec5.rec_code = sp.rec_village_idLEFT JOIN QY_PROTOCOL_MAIN t1 ON sp.basic_id = t1.basic_id AND t1.is_delete = '0' AND t1.PROTOCOL_STATE = '1' AND t1.source_signing = '1' WHEREsp.is_delete = '0' AND sp.status = '0' AND NVL( t1.id, '0' ) != '0' AND sp.rec_village_id IN ( SELECT DISTINCT t.REC_CODE FROM SYS_REC t JOIN SYS_MANAGE_REC t1 ON t1.rec_code = t.rec_code WHERE t1.org_code = '510185002001' ) ) SELECT'已签约' AS NAME,population_type AS typename,COUNT( * ) AS sum 
FROMfiltered_data 
GROUP BYpopulation_type 
ORDER BYtypename