工作和学习中常常会遇到一行要分割成多行数据的情况,在此整理一下做下对比。
单行拆分
如果表数据只有一行,则可以直接在原表上直接使用connect by+正则的方法,比如:
1 2 3 | select regexp_substr('444.555.666', '[^.]+', 1, level) col from dual connect by level egexp_count('444.555.666', '\.') + 1 |
输出结果:
1 2 3 4 5 | COL ---- 444 555 666 |
多行拆分
注意:下面方法可行,但有一个bug。数据表中有重复项时,此方法拆出来结果有问题,可以分组再使用,除了要拆分的字段,其他字段也可以加到select中。
如果数据表存在多行数据需要拆分,也可以在原表上使用connect+正则的方法:
方法一
1 2 3 4 5 6 7 8 9 10 11 | with t as (select '111.222.333' col from dual union all select '444.555.666' col from dual) select regexp_substr(col, '[^.]+', 1, level) from t connect by level = regexp_count(col, '\.\') + 1 and col = prior col and prior dbms_random.value > 0 |
结果:
1 2 3 4 5 6 7 | --------- 111 222 333 444 555 666 |
方法二
使用构造的最大行数值关联原表:
1 2 3 4 5 6 7 8 9 | with t as (select '111.222.333' col from dual union all select '444.555.666' col from dual) select regexp_substr(col, '[^.]+', 1, lv) from t, (select level lv from dual connect by level < 10) b where b.lv exp_count(t.col, '\.\') + 1 |
这种方法设置第二个数据集的时候要小于可能的最大值,然后两数据集做关联,在做大数据量拆分的时候,这个数值设置得当,拆分行数相对一致的情况下,效率比方法一直接connect by要高。
方法三
使用table函数:
1 2 3 4 5 6 7 8 9 10 11 12 13 | with t as (select '111.222.333' col from dual union all select '444.555.666' col from dual) select column_value from t, table(cast(multiset (select regexp_substr(col, '[^.]+', 1, level) dd from dual connect by level = regexp_count(t.col, '\.\') + 1) as sys.odcivarchar2list)) a |
结果:
1 2 3 4 5 6 7 8 | COLUMN_VALUE ------------- 111 222 333 444 555 666 |
这个方法输出的列名是固定的,column_value依赖于sys.odcivarchar2list这个类型的输出,该方法对于大数据量的拆分效率比第二个方法好。
方法四
1 2 3 4 5 6 7 8 9 | with t as (select '111.222.333' col from dual union all select '444.555.666' col from dual) select regexp_substr(col, '[^.]+', 1, trim(column_value)) from t, xmltable(concat('1 to ',regexp_count(t.col, '\.\') + 1)) a ; |
注意:大数据量的拆分时,谨慎使用正则的方法去做,可以使用substr+instr的方式替换正则。
如果以上方法的效率仍然不理想,可考虑使用plsql块。
=====================================================
下面是我实际使用的SQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | select check_classify 分类,resp_station 单位,sum(total) 总数量 from ( select t.check_classify,t.resp_station,count(id) total from aq_dynamic_check_info t where t.resp_station is not null and instr(t.resp_station,',') = 0 group by t.check_classify,t.resp_station union all select check_classify,regexp_substr(t.resp_station, '[^,]+', 1, level) resp_station,total from (select check_classify,resp_station,count(id) total from aq_dynamic_check_info where instr(resp_station,',') > 0 group by check_classify,resp_station) t connect by level and t.resp_station = prior t.resp_station and prior dbms_random.value > 0 ) A group by A.check_classify,A.resp_station order by sum(total) desc |