oracle 普通業務資料統計sql

標籤：span   delete   color   com   replace   har   varchar   tput   ora   

-- 字串分割樣本SELECT REGEXP_SUBSTR(‘17,20,23‘, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS STR    FROM DUAL  CONNECT BY LEVEL <=  LENGTH(‘17,20,23‘) - LENGTH(REGEXP_REPLACE(‘17,20,23‘, ‘,‘, ‘‘))+1;  CREATE TABLE tm_change(     ID NUMBER,     transit_zno VARCHAR2(10),     src VARCHAR2(100),     des VARCHAR2(100));CREATE TABLE temp_tm_change(     ID NUMBER,     transit_zno VARCHAR2(10),     src VARCHAR2(100),     des VARCHAR2(100));-- 原資料SELECT t.*,ROWID FROM tm_change t;--輸出資料SELECT t.* FROM temp_tm_change t;DELETE FROM temp_tm_change;COMMIT;DECLARE  recordCount NUMBER;-- 一條記錄衍生的記錄條數  sumCount NUMBER; --總條數BEGIN  sumCount := 0;  -- sum  FOR rs IN (SELECT id,transit_zno ,src,des FROM tm_change) LOOP      recordCount := 0;       --src       FOR srcRS IN (SELECT REGEXP_SUBSTR(rs.src, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS str            FROM DUAL  CONNECT BY LEVEL <=  LENGTH(rs.src) - LENGTH(REGEXP_REPLACE(rs.src, ‘,‘, ‘‘))+1) LOOP         -- des         FOR desRS IN (SELECT REGEXP_SUBSTR(rs.des, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS str              FROM DUAL  CONNECT BY LEVEL <=  LENGTH(rs.des) - LENGTH(REGEXP_REPLACE(rs.des, ‘,‘, ‘‘))+1) LOOP              recordCount := recordCount + 1;             sumCount := sumCount + 1;             INSERT INTO temp_tm_change(ID,transit_zno,src,des) VALUES (rs.id,rs.transit_zno,srcRs.str,desRS.str);             COMMIT;         END LOOP;       END LOOP;       --dbms_output.put_line(‘insert a record : id ->‘ || rs.id || ‘  recordCount --> ‘ || recordCount ); buffer overflow  END LOOP;  --dbms_output.put_line(‘create  record  sumCount --> ‘ || sumCount );END;SELECT transit_zno,src,des FROM temp_tm_change GROUP BY transit_zno,src,des HAVING COUNT(*) > 1 ;  

 

oracle 普通業務資料統計sql