我有表tab1,里面有n,m两个字段
n m
0001 10001
0001 10002
0001 10003
0001 10005
0001 10006
0004 40001
0004 40002
.... ....我要的结果
0001 10001 10003
0001 10005 10006
0004 40001 40002
n m
0001 10001
0001 10002
0001 10003
0001 10005
0001 10006
0004 40001
0004 40002
.... ....我要的结果
0001 10001 10003
0001 10005 10006
0004 40001 40002
create table tab1(n varchar2(10),m varchar2(10));
insert into tab1 values('0001','10001');
insert into tab1 values('0001','10002');
insert into tab1 values('0001','10003');
insert into tab1 values('0001','10005');
insert into tab1 values('0001','10006');
insert into tab1 values('0004','40001');
insert into tab1 values('0004','40002');
commit;select n,min(m) m1,max(m) m2 from (
select n,m,sum(m1) over(order by n,m) mf from(
select n,m,
decode(m-lag(m,1,0) over (partition by n order by n,m),1,0,1) m1
from tab1
)
)group by n,mf;N M1 M2
---------- ---------- ----------
0001 10001 10003
0001 10005 10006
0004 40001 40002
from tab1 t1,tab1 t2
where t1.n=t2.n and t1.m=t2.m-1
group by n没测试,不知道可不可以。
FROM (SELECT a.*, TO_NUMBER (a.m- ROWNUM) cc
FROM (SELECT *
FROM t
ORDER BY n, m) a) b
GROUP BY b.n, b.cc