某表有三字段
ID--流水
SN--起始号码
EN--结束号码记录表现方式
1 1 2
2 3 4
3 5 6
4 9 10
求帮一SQL,让查询结果集为
1,6
9,10
关键问题就是判断号码是否为连续号码,是连续得号码就取其一条记录.
高手指教,高分相送!!
ID--流水
SN--起始号码
EN--结束号码记录表现方式
1 1 2
2 3 4
3 5 6
4 9 10
求帮一SQL,让查询结果集为
1,6
9,10
关键问题就是判断号码是否为连续号码,是连续得号码就取其一条记录.
高手指教,高分相送!!
insert into t select 1,1,2
insert into t select 2,3,4
insert into t select 3,5,6
insert into t select 4,9,10select
a.SN,min(b.EN) EN
from
(select m.SN from t m where not exists(select * from t where EN=m.SN-1)) a,
(select n.EN from t n where not exists(select * from t where SN=n.EN+1)) b
where
a.SN<b.EN
group by
a.SN
insert into t select 1,1,2
insert into t select 2,3,4
insert into t select 3,5,6
insert into t select 4,9,10
goselect
a.SN,min(b.EN) EN
from
(select m.SN from t m where not exists(select * from t where EN=m.SN-1)) a,
(select n.EN from t n where not exists(select * from t where SN=n.EN+1)) b
where
a.SN<b.EN
group by
a.SN
go/*SN EN
----------- -----------
1 6
9 10
*/drop table t
go
因为没有用到特殊的T-SQL语法,以上SQL在Oracle的PL/SQL环境下应该可用。
SQL> select
2 a.SN,min(b.EN) EN
3 from
4 (select m.SN from t m where not exists(select * from t where EN=m.SN-1)) a,
5 (select n.EN from t n where not exists(select * from t where SN=n.EN+1)) b
6 where
7 a.SN<b.EN
8 group by
9 a.SN
10 / SN EN
--------- ---------
1 6
9 10SQL>