表:temp_test
有以下数据:
num1
------
1
3
4
5
7
10
11
20
现在,要写一段SQL,检索的结果为:
1
3
5
7
10
11
20也就是,结果集中的数据,只有连续行中的开始和结束数据。!求解!
---------------------------
这是我最近遇到的。
有以下数据:
num1
------
1
3
4
5
7
10
11
20
现在,要写一段SQL,检索的结果为:
1
3
5
7
10
11
20也就是,结果集中的数据,只有连续行中的开始和结束数据。!求解!
---------------------------
这是我最近遇到的。
from ( select t.num1
, t.num1 - rownum flag
from ( select num1 from temp_test order by num1) t
)
group by flag
union
select max(num1) num1
from ( select t.num1
, t.num1 - rownum flag
from ( select num1 from temp_test order by num1) t
)
group by flag;
create table t (num number);begin
insert into t values(1);
insert into t values(3);
insert into t values(4);
insert into t values(5);
insert into t values(7);
insert into t values(8);
insert into t values(9);
insert into t values(11);
insert into t values(15);
insert into t values(16);
insert into t values(17);
insert into t values(18);
insert into t values(20);
end;select num
from t
where num not in
(select num
from (select num, next_num - num gapi, num - pre_num gapii
from (select num,
lead(num, 1, null) over(order by num) next_num,
lag(num, 1, null) over(order by num) pre_num
from t))
where gapi = 1
and gapii = 1)
order by num
谢谢了。
不用not in 可以写成这样:
select num1
from (select num1,
nvl(next_num - num1, 0) gapi,
nvl(num1 - pre_num, 0) gapii
from (select num1,
lead(num1) over(order by num1) next_num,
lag(num1) over(order by num1) pre_num
from temp_test))
where gapi <> 1
or gapii <> 1