去除开头和结尾的0，

去除开头和结尾的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);