去除开头和结尾的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);
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq646748739/article/details/78209855
文章标签: Oracle
个人分类: Oracle实验例子
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭