with t1 as (select A,B,row_number() over(partition by B order by A) r1,A-min(A) over(partition by B)+1 r2 from table), t2 as (select distinct min(A) over(partition by B,r2-r1) C,max(A) over(partition by B,r2-r1) D,B from t1 order by 1,2 ) select case C=D then C else C||'~'||D as A,B from t2 order by 1,2
SQL> SQL> with tmp(a,b) as 2 ( 3 select 1,'M' from dual union all 4 select 2,'M' from dual union all 5 select 3,'M' from dual union all 6 select 4,'N' from dual union all 7 select 5,'M' from dual union all 8 select 6,'P' from dual union all 9 select 7,'P' from dual union all 10 select 8,'Q'from dual 11 ) 12 select * from 13 (select min(a)||case when min(a)=max(a) then null else '-'||max(a) end as a,b from 14 (select t.*,nvl(t.a-lag(a)over(partition by b order by a),1) as c from tmp t) 15 group by b,c) 16 order by a 17 / A B -------------------------------------------------------------------------------- - 1-3 M 4 N 5 M 6-7 P 8 Q
SQL> with tmp(a,b) as 2 ( 3 select 1,'M' from dual union all 4 select 2,'M' from dual union all 5 select 3,'M' from dual union all 6 select 4,'M' from dual union all 7 select 5,'N' from dual union all 8 select 6,'M' from dual union all 9 select 7,'P' from dual union all 10 select 8,'P' from dual union all 11 select 9,'Q'from dual union all 12 select 10,'M' from dual union all 13 select 11,'M' from dual union all 14 select 12,'M' from dual 15 ) 16 select * from 17 (select min(a)||case when min(a)=max(a) then null else '-'||max(a) end as a,b from 18 (select t.*,nvl(t.a-lag(a)over(partition by b order by a),1) as c from tmp t) 19 group by b,c) 20 order by a; A B -------------------------------------------------------------------------------- - 1-12 M 10 M 5 N 6 M 7-8 P 9 Q 6 rows selected 接着加下去就有问题了
with tmp as ( select 1 as a,'M' as b from dual union all select 2,'M' from dual union all select 3,'M' from dual union all select 4,'N' from dual union all select 5,'M' from dual union all select 6,'P' from dual union all select 7,'P' from dual union all select 8,'Q'from dual union all select 9,'Q'from dual union all select 10,'M' from dual union all select 11,'M' from dual union all select 12,'M' from dual )select min(no)|| case when max(no) =min(no) then null else '-'||max(no) end,b from ( SELECT no, b, myCount FROM tmp MODEL DIMENSION BY (rownum as no) MEASURES ( b, 1 AS myCount) RULES ( myCount[ANY] = CASE WHEN b[CURRENTV()] = b[CURRENTV()-1]THEN myCount[CURRENTV()-1] + 1 ELSE 1 END )) a group by b,mycount-no order by to_number(min(no))||'-'||to_number(max(no))
with tmp as ( select 1 as a,'M' as b from dual union all select 2,'M' from dual union all select 3,'M' from dual union all select 4,'N' from dual union all select 5,'M' from dual union all select 6,'P' from dual union all select 7,'P' from dual union all select 8,'Q'from dual union all select 9,'Q'from dual union all select 10,'M' from dual union all select 11,'M' from dual union all select 12,'M' from dual )select min(no)|| case when max(no) =min(no) then null else '-'||max(no) end,b from ( SELECT no, b, myCount FROM tmp MODEL DIMENSION BY (rownum as no) MEASURES ( b, 1 AS myCount) RULES ( myCount[ANY] = CASE WHEN b[CURRENTV()] = b[CURRENTV()-1]THEN myCount[CURRENTV()-1] + 1 ELSE 1 END )) a group by b,mycount-no order by min(no)
t2 as (select distinct min(A) over(partition by B,r2-r1) C,max(A) over(partition by B,r2-r1) D,B from t1 order by 1,2 )
select case C=D then C else C||'~'||D as A,B from t2 order by 1,2
SQL> with tmp(a,b) as
2 (
3 select 1,'M' from dual union all
4 select 2,'M' from dual union all
5 select 3,'M' from dual union all
6 select 4,'N' from dual union all
7 select 5,'M' from dual union all
8 select 6,'P' from dual union all
9 select 7,'P' from dual union all
10 select 8,'Q'from dual
11 )
12 select * from
13 (select min(a)||case when min(a)=max(a) then null else '-'||max(a) end as a,b from
14 (select t.*,nvl(t.a-lag(a)over(partition by b order by a),1) as c from tmp t)
15 group by b,c)
16 order by a
17 /
A B
-------------------------------------------------------------------------------- -
1-3 M
4 N
5 M
6-7 P
8 Q
2 (
3 select 1,'M' from dual union all
4 select 2,'M' from dual union all
5 select 3,'M' from dual union all
6 select 4,'M' from dual union all
7 select 5,'N' from dual union all
8 select 6,'M' from dual union all
9 select 7,'P' from dual union all
10 select 8,'P' from dual union all
11 select 9,'Q'from dual union all
12 select 10,'M' from dual union all
13 select 11,'M' from dual union all
14 select 12,'M' from dual
15 )
16 select * from
17 (select min(a)||case when min(a)=max(a) then null else '-'||max(a) end as a,b from
18 (select t.*,nvl(t.a-lag(a)over(partition by b order by a),1) as c from tmp t)
19 group by b,c)
20 order by a;
A B
-------------------------------------------------------------------------------- -
1-12 M
10 M
5 N
6 M
7-8 P
9 Q
6 rows selected
接着加下去就有问题了
(
select 1 as a,'M' as b from dual union all
select 2,'M' from dual union all
select 3,'M' from dual union all
select 4,'N' from dual union all
select 5,'M' from dual union all
select 6,'P' from dual union all
select 7,'P' from dual union all
select 8,'Q'from dual union all
select 9,'Q'from dual union all
select 10,'M' from dual union all
select 11,'M' from dual union all
select 12,'M' from dual
)select min(no)|| case when max(no) =min(no) then null else '-'||max(no) end,b from (
SELECT
no,
b,
myCount
FROM
tmp
MODEL
DIMENSION BY (rownum as no)
MEASURES ( b, 1 AS myCount)
RULES (
myCount[ANY] =
CASE
WHEN b[CURRENTV()] = b[CURRENTV()-1]THEN myCount[CURRENTV()-1] + 1
ELSE 1
END
)) a group by b,mycount-no order by to_number(min(no))||'-'||to_number(max(no))
(
select 1 as a,'M' as b from dual union all
select 2,'M' from dual union all
select 3,'M' from dual union all
select 4,'N' from dual union all
select 5,'M' from dual union all
select 6,'P' from dual union all
select 7,'P' from dual union all
select 8,'Q'from dual union all
select 9,'Q'from dual union all
select 10,'M' from dual union all
select 11,'M' from dual union all
select 12,'M' from dual
)select min(no)|| case when max(no) =min(no) then null else '-'||max(no) end,b from (
SELECT
no,
b,
myCount
FROM
tmp
MODEL
DIMENSION BY (rownum as no)
MEASURES ( b, 1 AS myCount)
RULES (
myCount[ANY] =
CASE
WHEN b[CURRENTV()] = b[CURRENTV()-1]THEN myCount[CURRENTV()-1] + 1
ELSE 1
END
)) a group by b,mycount-no order by min(no)