11:31:44 SQL> select * from tt; A
----------
10
12
13
14
15
20
2已选择7行。已用时间: 00: 00: 00.32
11:31:57 SQL> select t1.a,t2.a+1 from (select rownum id,a from (select a from tt order by a)) t1,
11:32:50 2 (select rownum-1 id,a from (select a-1 a from tt order by a-1)) t2
11:32:53 3 where t1.id=t2.id(+) and t1.a<>t2.a; A T2.A+1
---------- ----------
2 10
10 12
15 20已用时间: 00: 00: 00.31
11:32:53 SQL>
----------
10
12
13
14
15
20
2已选择7行。已用时间: 00: 00: 00.32
11:31:57 SQL> select t1.a,t2.a+1 from (select rownum id,a from (select a from tt order by a)) t1,
11:32:50 2 (select rownum-1 id,a from (select a-1 a from tt order by a-1)) t2
11:32:53 3 where t1.id=t2.id(+) and t1.a<>t2.a; A T2.A+1
---------- ----------
2 10
10 12
15 20已用时间: 00: 00: 00.31
11:32:53 SQL>
1,2,3,4,5,7,8,9,11,12,23
结果应该为
1-5
7-9
11-12,
23-23
create table tt
(
a number(20)
);
插入数据:
1,2,3,4,5,7,8,9,11,12,23
查询语句:
SELECT T3.A1, T4.A2 - 1
FROM (SELECT ROWNUM AS id1, tt.a AS a1
FROM tt
WHERE (NOT EXISTS
(SELECT 'x'
FROM (SELECT tt.a + 1 AS a2
FROM tt
ORDER BY tt.a + 1) t2
WHERE tt.a = t2.a2))
ORDER BY tt.a) T3,
(SELECT ROWNUM AS ID3, A2
FROM (SELECT tt.a + 1 AS a2
FROM tt
ORDER BY tt.a + 1) t2
WHERE (NOT EXISTS
(SELECT 'X'
FROM TT
WHERE T2.A2 = TT.A))) T4
WHERE T3.ID1 = T4.ID3;
执行结果:
A1 T4.A2-1
---------- ----------
1 5
7 9
11 12
23 23楼主看看,行否
比如table a { int b}
1,2,3,4,5,7,8,9,11,12,23
结果应该为
1-5
7-9
11-12,
23-23
*/create table a(b number(10));
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into a values(4);
insert into a values(5);
insert into a values(7);
insert into a values(8);
insert into a values(9);
insert into a values(11);
insert into a values(12);
insert into a values(23);select * from a;select t2.b,t3.b,t2.b || '-' || t3.b from
(
select rownum id,B from a t1 where
not exists(select 1 from a where b = t1.b - 1)
order by b
) t2
,
(
select rownum id,B from a t1 where
not exists(select 1 from a where b = t1.b + 1)
order by b
) t3
where t2.id = t3.id
/
drop table a
/