按你给定的数据测试通过,到实际环境执行下试试吧 SQL> WITH Table1 AS( 2 select 'A' A,1 B FROM DUAL UNION ALL 3 select 'A' A,2 B FROM DUAL UNION ALL 4 select 'A' A,3 B FROM DUAL UNION ALL 5 select 'A' A,5 B FROM DUAL UNION ALL 6 select 'A' A,6 B FROM DUAL UNION ALL 7 select 'B' A,1 B FROM DUAL UNION ALL 8 select 'B' A,2 B FROM DUAL UNION ALL 9 select 'C' A,1 B FROM DUAL UNION ALL 10 select 'C' A,2 B FROM DUAL UNION ALL 11 select 'C' A,3 B FROM DUAL UNION ALL 12 select 'C' A,4 B FROM DUAL UNION ALL 13 select 'C' A,8 B FROM DUAL UNION ALL 14 select 'C' A,9 B FROM DUAL UNION ALL 15 select 'C' A,10 B FROM DUAL 16 ),T AS ( 17 SELECT A,B,LAG(B,1,B-2)OVER(PARTITION BY A ORDER BY B) PRE 18 FROM Table1 19 ),T1 AS ( 20 SELECT A,CONNECT_BY_ROOT(B)||'-'||B B FROM T 21 WHERE CONNECT_BY_ISLEAF=1 22 START WITH B-PRE>1 23 CONNECT BY PRIOR A=A AND PRIOR B+1=B 24 ) 25 SELECT A,TO_CHAR(WMSYS.WM_CONCAT(B)) B 26 FROM T1 GROUP BY A;A - B -------------------------------------------------------------------------------- A 1-3,5-6B 1-2C 1-4,8-10
select a, wm_concat(b_range) from (select a, min(b) || '-' || max(b) b_range from (select a, b, dense_rank() over(partition by a order by b) dr from t) group by a, dr - b) group by a
SQL> WITH Table1 AS(
2 select 'A' A,1 B FROM DUAL UNION ALL
3 select 'A' A,2 B FROM DUAL UNION ALL
4 select 'A' A,3 B FROM DUAL UNION ALL
5 select 'A' A,5 B FROM DUAL UNION ALL
6 select 'A' A,6 B FROM DUAL UNION ALL
7 select 'B' A,1 B FROM DUAL UNION ALL
8 select 'B' A,2 B FROM DUAL UNION ALL
9 select 'C' A,1 B FROM DUAL UNION ALL
10 select 'C' A,2 B FROM DUAL UNION ALL
11 select 'C' A,3 B FROM DUAL UNION ALL
12 select 'C' A,4 B FROM DUAL UNION ALL
13 select 'C' A,8 B FROM DUAL UNION ALL
14 select 'C' A,9 B FROM DUAL UNION ALL
15 select 'C' A,10 B FROM DUAL
16 ),T AS (
17 SELECT A,B,LAG(B,1,B-2)OVER(PARTITION BY A ORDER BY B) PRE
18 FROM Table1
19 ),T1 AS (
20 SELECT A,CONNECT_BY_ROOT(B)||'-'||B B FROM T
21 WHERE CONNECT_BY_ISLEAF=1
22 START WITH B-PRE>1
23 CONNECT BY PRIOR A=A AND PRIOR B+1=B
24 )
25 SELECT A,TO_CHAR(WMSYS.WM_CONCAT(B)) B
26 FROM T1 GROUP BY A;A
-
B
--------------------------------------------------------------------------------
A
1-3,5-6B
1-2C
1-4,8-10
from (select a, min(b) || '-' || max(b) b_range
from (select a, b, dense_rank() over(partition by a order by b) dr
from t)
group by a, dr - b)
group by a