已知:
A B
1 0
2 0
3 1
4 2
5 0
6 0
7 3
8 0
9 0
开头和结尾的0都去除(包括连续的),中间的0不去除
最终的效果如下:
A B
3 1
4 2
5 0
6 0
7 3
create table test as
select 1 A, 0 B from dual union all
select 2 A, 0 B from dual union all
select 3 A, 1 B from dual union all
select 4 A, 2 B from dual union all
select 5 A, 0 B from dual union all
select 6 A, 0 B from dual union all
select 7 A, 3 B from dual union all
select 8 A, 0 B from dual union all
select 9 A, 0 B from dual;
--方法1:
with tmp as
(select a,row_number() over(order by rn desc, a) as rn
from(select a,a-row_number()over(order by a) rn from test where b=0)
)
select *
from test
where a between (select a+1 from tmp where rn = (select max(rn) from tmp)) and (select a-1 from tmp where rn = (select min(rn) from tmp));
--方法2:
select * from test where a between (select min(a) from test where b <> 0) and (select max(a) from test where b <> 0);
A B
1 0
2 0
3 1
4 2
5 0
6 0
7 3
8 0
9 0
开头和结尾的0都去除(包括连续的),中间的0不去除
最终的效果如下:
A B
3 1
4 2
5 0
6 0
7 3
create table test as
select 1 A, 0 B from dual union all
select 2 A, 0 B from dual union all
select 3 A, 1 B from dual union all
select 4 A, 2 B from dual union all
select 5 A, 0 B from dual union all
select 6 A, 0 B from dual union all
select 7 A, 3 B from dual union all
select 8 A, 0 B from dual union all
select 9 A, 0 B from dual;
--方法1:
with tmp as
(select a,row_number() over(order by rn desc, a) as rn
from(select a,a-row_number()over(order by a) rn from test where b=0)
)
select *
from test
where a between (select a+1 from tmp where rn = (select max(rn) from tmp)) and (select a-1 from tmp where rn = (select min(rn) from tmp));
--方法2:
select * from test where a between (select min(a) from test where b <> 0) and (select max(a) from test where b <> 0);