oracle 普通业务数据统计sql
时间：2021-07-01 10:21:17
帮助过：69人阅读
字符串分割示例
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
标签：span delete color com replace har varchar tput ora
