oracle按区间分组,关于按区间分组的问题(更正了下问题)

本帖最后由 asamiya310 于 2013-9-23 15:28 编辑

不知道是否想的复杂了，坐等大神的更简洁更好地解法

WITH A AS

(SELECT 'A1' ID,1 OPENNUM,7 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A2' ID,9 OPENNUM,15 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A3' ID,2 OPENNUM,5 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A4' ID,3 OPENNUM,7 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A5' ID,8 OPENNUM,11 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A6' ID,12 OPENNUM,14 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A7' ID,1 OPENNUM,13 CLOSENUM FROM DUAL

UNION ALL

SELECT 'A8' ID,100 OPENNUM,180 CLOSENUM FROM DUAL

/*UNION ALL

SELECT 'A9' ID,100 OPENNUM,190 CLOSENUM FROM DUAL*/) --测试数据

,B AS --计算相交的各种情况，并产生层次关系

(

SELECT A1.ID ID1,A1.OPENNUM OPENNUM1,A1.CLOSENUM CLOSENUM1,A2.ID ID2,A2.OPENNUM OPENNUM2,A2.CLOSENUM CLOSENUM2

,LEAST(A1.OPENNUM,A2.OPENNUM) MIN_OPEN,GREATEST(A1.CLOSENUM,A2.CLOSENUM) MAX_CLOSE

FROM A A1,A A2

WHERE A2.OPENNUM BETWEEN A1.OPENNUM AND A1.CLOSENUM

--AND A1.ID <> A2.ID --注释掉是为了防止只有单个元素为一组的情况

)

,REC(ID1,ID2,OPENNUM,CLOSENUM,LVL,RT,PATH,RT_OPEN,PATH_CLOSE) AS --递归查找每一个元素的根(最小)和叶子(最大)

(SELECT B.ID1,B.ID2,B.OPENNUM1,B.CLOSENUM1,1 LVL,B.ID1 RT,B.ID1,MIN_OPEN,MAX_CLOSE PATH FROM B

UNION ALL

SELECT B.ID1,B.ID2,B.OPENNUM1,B.CLOSENUM1,LVL + 1,REC.RT,B.ID2,LEAST(B.MIN_OPEN,REC.RT_OPEN),GREATEST(B.MAX_CLOSE,REC.PATH_CLOSE) FROM REC,B

WHERE REC.ID2 = B.ID1)

CYCLE ID1 SET DUP_ID TO 'Y' DEFAULT 'N'

,D AS --获得区间

(SELECT DISTINCT (SELECT MIN(RT_OPEN) FROM REC WHERE T.RT_OPEN >= RT_OPEN AND T.PATH_CLOSE <= PATH_CLOSE) MIN_OPEN

,(SELECT MAX(PATH_CLOSE) FROM REC WHERE T.RT_OPEN >= RT_OPEN AND T.PATH_CLOSE <= PATH_CLOSE) MAX_CLOSE

FROM REC T)

--计算每个元素所在的区间，并按区间分组

SELECT D.MIN_OPEN,D.MAX_CLOSE,LISTAGG(ID,',') WITHIN GROUP(ORDER BY ID) FROM D,A

WHERE A.OPENNUM >= D.MIN_OPEN AND A.CLOSENUM <= D.MAX_CLOSE

GROUP BY D.MIN_OPEN,D.MAX_CLOSE;