我有像
periodbal balancetype
------------- -------------
0;15;11;-13;-16;20 ABS
22;25;-78;0;1 ABS
67;89;-36;83;90;55 ABS
… ACS
…
我想找
periodbal balancetype
--------- -------------
20 ABS
25 ABS
90 ABS
解决方法:
这里的想法是:
对于balancetype的每个值,请从periodbal列中提取所有值,将它们转换为单独的行,然后为每行的balancetype值计算最大值.
即
PERIODBAL BALANCETYPE ID
0 ABS 1
15 ABS 1
11 ABS 1
-13 ABS 1
-16 ABS 1
20 ABS 1
第2行分割
22 ABS 2
25 ABS 2
-78 ABS 2
0 ABS 2
1 ABS 2
......
最后,获取MAX(PERIODBAL)并按列balancetype,ID将数据分组
CREATE TABLE SYS.TEST
(
PERIODBAL VARCHAR2(50 BYTE),
BALANCETYPE VARCHAR2(5 BYTE)
)
INSERT INTO TEST VALUES ('0;15;11;-13;-16;20', 'ABS');
INSERT INTO TEST VALUES ('22;25;-78;0;1', 'ABS');
INSERT INTO TEST VALUES ('67;89;-36;83;90;55 ', 'ABS');
INSERT INTO TEST VALUES ('0;15;10;-13;-16;23', 'ACS');
INSERT INTO TEST VALUES ('0;14;11;-13;-16;25', 'ACS');
解:
SELECT BALANCETYPE,MAX(BAL) AS PERIODBAL
FROM
(
SELECT SUB1.*, TRIM(REGEXP_SUBSTR( PERIODBAL, '[^;]+', 1, LVL)) BAL
FROM (SELECT TEST.*, ROW_NUMBER() OVER (ORDER BY BALANCETYPE ) UNIQ_ID FROM TEST) SUB1 ,
(SELECT LEVEL LVL FROM DUAL,(SELECT MAX(LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+')))+1 AS MAX_BAL FROM TEST) TEMP
CONNECT BY LEVEL<= TEMP.MAX_BAL ) SUB2
WHERE LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+'))+1>= SUB2.LVL
) GROUP BY BALANCETYPE, UNIQ_ID
输出:
BALANCETYPE PERIODBAL
ACS 23
ABS 20
ABS 25
ABS 90
ACS 25
希望能有所帮助,谢谢.