SQL> with T1 AS ( 2 SELECT 3000000035 ID,'a' NAME FROM DUAL UNION ALL 3 SELECT 3000000033 ID,'b' NAME FROM DUAL UNION ALL 4 SELECT 9109001544 ID,'c' NAME FROM DUAL UNION ALL 5 SELECT 9109001550 ID,'d' NAME FROM DUAL UNION ALL 6 SELECT 9109001545 ID,'e' NAME FROM DUAL UNION ALL 7 SELECT 9109001551 ID,'f' NAME FROM DUAL UNION ALL 8 SELECT 9109441531 ID,'g' NAME FROM DUAL 9 ),T AS ( 10 select '(3000000035-3000000033)/(9109001544+9109001550+9109001545+9109001551)* 9109441531' str from dual 11 ),T2 AS ( 12 SELECT 'SUM('||T1.NAME||')' STR,A.L FROM( 13 SELECT REGEXP_SUBSTR(STR,'[0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[0-9]+',1,LEVEL) L FROM T 14 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+') 15 )A,T1 WHERE A.STR=T1.ID 16 UNION ALL 17 SELECT REGEXP_SUBSTR(STR,'[^0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[^0-9]+',1,LEVEL) L FROM T 18 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+') 19 ) 20 SELECT LISTAGG(STR,'')WITHIN GROUP(ORDER BY L) 21 FROM T2;LISTAGG(STR,'')WITHINGROUP(ORDERBYL) -------------------------------------------------------------------------------- (SUM(a)-SUM(b))/(SUM(c)+SUM(d)+SUM(e)+SUM(f))* SUM(g)
同理拼接case when的条件,然后将两部分拼接在一起就可以了 SQL> with T1 AS ( 2 SELECT 3000000035 ID,'a' NAME FROM DUAL UNION ALL 3 SELECT 3000000033 ID,'b' NAME FROM DUAL UNION ALL 4 SELECT 9109001544 ID,'c' NAME FROM DUAL UNION ALL 5 SELECT 9109001550 ID,'d' NAME FROM DUAL UNION ALL 6 SELECT 9109001545 ID,'e' NAME FROM DUAL UNION ALL 7 SELECT 9109001551 ID,'f' NAME FROM DUAL UNION ALL 8 SELECT 9109441531 ID,'g' NAME FROM DUAL 9 ),T AS ( 10 SELECT SUBSTR(STR,3,LENGTH(STR)-3) STR FROM(SELECT REGEXP_SUBSTR(STR,'/[(].*[)]',1,1) STR FROM 11 (SELECT '(3000000035-3000000033)/(9109001544+9109001550+9109001545+9109001551)* 9109441531' STR FROM DUAL)) 12 ),T2 AS ( 13 SELECT 'SUM('||T1.NAME||')' STR,A.L FROM( 14 SELECT REGEXP_SUBSTR(STR,'[0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[0-9]+',1,LEVEL) L FROM T 15 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+') 16 )A,T1 WHERE A.STR=T1.ID 17 UNION ALL 18 SELECT REGEXP_SUBSTR(STR,'[^0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[^0-9]+',1,LEVEL) L FROM T 19 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+') 20 ) 21 SELECT LISTAGG(STR,'')WITHIN GROUP(ORDER BY L) 22 FROM T2;LISTAGG(STR,'')WITHINGROUP(ORDERBYL) -------------------------------------------------------------------------------- SUM(c)+SUM(d)+SUM(e)+SUM(f)
厉害,正则用的少,不太会 SELECT REGEXP_SUBSTR(STR,'[^0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[^0-9]+',1,LEVEL) L FROM T 18 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+') ,我这里做了个小修改CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]'),这样的话对(3000000035-3000000033)/(9109001544+9109001550+9109001545+9109001551)也可以实现。
改为CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[^0-9]+')更合适
对应关系是这种,一个id对应一个name
2 SELECT 3000000035 ID,'a' NAME FROM DUAL UNION ALL
3 SELECT 3000000033 ID,'b' NAME FROM DUAL UNION ALL
4 SELECT 9109001544 ID,'c' NAME FROM DUAL UNION ALL
5 SELECT 9109001550 ID,'d' NAME FROM DUAL UNION ALL
6 SELECT 9109001545 ID,'e' NAME FROM DUAL UNION ALL
7 SELECT 9109001551 ID,'f' NAME FROM DUAL UNION ALL
8 SELECT 9109441531 ID,'g' NAME FROM DUAL
9 ),T AS (
10 select '(3000000035-3000000033)/(9109001544+9109001550+9109001545+9109001551)* 9109441531' str from dual
11 ),T2 AS (
12 SELECT 'SUM('||T1.NAME||')' STR,A.L FROM(
13 SELECT REGEXP_SUBSTR(STR,'[0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[0-9]+',1,LEVEL) L FROM T
14 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+')
15 )A,T1 WHERE A.STR=T1.ID
16 UNION ALL
17 SELECT REGEXP_SUBSTR(STR,'[^0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[^0-9]+',1,LEVEL) L FROM T
18 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+')
19 )
20 SELECT LISTAGG(STR,'')WITHIN GROUP(ORDER BY L)
21 FROM T2;LISTAGG(STR,'')WITHINGROUP(ORDERBYL)
--------------------------------------------------------------------------------
(SUM(a)-SUM(b))/(SUM(c)+SUM(d)+SUM(e)+SUM(f))* SUM(g)
SQL> with T1 AS (
2 SELECT 3000000035 ID,'a' NAME FROM DUAL UNION ALL
3 SELECT 3000000033 ID,'b' NAME FROM DUAL UNION ALL
4 SELECT 9109001544 ID,'c' NAME FROM DUAL UNION ALL
5 SELECT 9109001550 ID,'d' NAME FROM DUAL UNION ALL
6 SELECT 9109001545 ID,'e' NAME FROM DUAL UNION ALL
7 SELECT 9109001551 ID,'f' NAME FROM DUAL UNION ALL
8 SELECT 9109441531 ID,'g' NAME FROM DUAL
9 ),T AS (
10 SELECT SUBSTR(STR,3,LENGTH(STR)-3) STR FROM(SELECT REGEXP_SUBSTR(STR,'/[(].*[)]',1,1) STR FROM
11 (SELECT '(3000000035-3000000033)/(9109001544+9109001550+9109001545+9109001551)* 9109441531' STR FROM DUAL))
12 ),T2 AS (
13 SELECT 'SUM('||T1.NAME||')' STR,A.L FROM(
14 SELECT REGEXP_SUBSTR(STR,'[0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[0-9]+',1,LEVEL) L FROM T
15 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+')
16 )A,T1 WHERE A.STR=T1.ID
17 UNION ALL
18 SELECT REGEXP_SUBSTR(STR,'[^0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[^0-9]+',1,LEVEL) L FROM T
19 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+')
20 )
21 SELECT LISTAGG(STR,'')WITHIN GROUP(ORDER BY L)
22 FROM T2;LISTAGG(STR,'')WITHINGROUP(ORDERBYL)
--------------------------------------------------------------------------------
SUM(c)+SUM(d)+SUM(e)+SUM(f)
SELECT REGEXP_SUBSTR(STR,'[^0-9]+',1,LEVEL) STR,REGEXP_INSTR(STR,'[^0-9]+',1,LEVEL) L FROM T
18 CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]+') ,我这里做了个小修改CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[0-9]'),这样的话对(3000000035-3000000033)/(9109001544+9109001550+9109001545+9109001551)也可以实现。