【在线等，急急急】 Oracle的SQL语句查询

[Nullo_o]

A B
1 0
2 0
3 12
4 0
5 5
6 0

AB两列 怎么去除开头和末尾的0 ，
0可能会连续，中间的0不去除

[Nullo_o]

没有人吗 ？ 求告知

[碧水幽幽泉]

楼主，你试试上述SQL看看。

[Nullo_o]

引用 1 楼 qq646748739 的回复:

select a,b
from(
select a,b,
           row_number()over(order by a) rn1,
           row_number()over(order by a dec) rn2
  from table_name
  ) where rn1 <> 1
      and rn2 <>1

这样的话 不是只去除的第一个和最后一个吗，我想去除的是：如果开头第一行为0后面第二行也是0的话一并去除

[Nullo_o]

引用 5 楼 qq646748739 的回复:

没问题的话，就可以结贴了。

如果开头第一行为0后面第二行也是0的话一并去除 就是开头和结尾 连续的0都去除

[碧水幽幽泉]

select a,b
from(
select a,b,
           row_number()over(order by a) rn1,
           row_number()over(order by a dec) rn2
  from table_name
  ) where rn1 <> 1
      and rn2 <>1

[碧水幽幽泉]

我试了下，是OK的

create table tmp as
select 1 A, 0 B from dual union all
select 2 A, 0 B from dual union all
select 3 A, 12 B from dual union all
select 4 A, 0 B from dual union all
select 5 A, 5 B from dual union all
select 6 A, 0 B from dual;

select a,b
from(
select a,b,
           row_number()over(order by a) rn1,
           row_number()over(order by a desc) rn2
  from tmp
  ) 
  where rn1 <> 1
      and rn2 <>1
    order by a;

[碧水幽幽泉]

没问题的话，就可以结贴了。

[听雨停了]

引用 7 楼 qq_28332767 的回复:

[quote=引用 5 楼 qq646748739 的回复:] 没问题的话，就可以结贴了。

如果开头第一行为0后面第二行也是0的话一并去除 就是开头和结尾 连续的0都去除[/quote]

--测试数据
IF OBJECT_ID('#tab') IS NOT NULL
	DROP TABLE #tab
CREATE TABLE #tab(
	a INT,
	b int	
)
INSERT INTO #tab
SELECT 1,0 UNION ALL
SELECT 2,0 UNION ALL
SELECT 3,9 UNION ALL
SELECT 4,0 UNION ALL
SELECT 5,8 UNION ALL
SELECT 6,0 UNION ALL
SELECT 7,0
--测试数据结束

--Oracle很少用，大概的思路就是下面这样的。不知道这MSSql的你能不能看懂哈
--步骤2：以下面的子查询结果为基础，按rn倒序，a正序再产生一个新的rownumber(新rn)，结果集插入一个临时表#Tab2
SELECT a,
       ROW_NUMBER() OVER(ORDER BY rn DESC, a) AS rn
--INTO   #tab2
FROM   (   
			--步骤1：找出所有b字段为0的数据，a减去rownumber(按a字段排序产生一个rownumber)得到一个值rn
           SELECT a,
                  a -ROW_NUMBER() OVER(ORDER BY a) AS rn
           FROM   #tab
           WHERE  b = 0
			--           a           rn
			------------- --------------------
			--1           0
			--2           0
			--4           1
			--6           2
			--7           2
       ) a
--       a           rn
------------- --------------------
--6           1
--7           2
--4           3
--1           4
--2           5

--步骤3：Between最大的rn值对应的a字段的值+1(也就是3) And 最小的rn的值对应的a字段的值-1(也就是5)
SELECT *
FROM   #tab
WHERE  a BETWEEN 
       (
           SELECT a + 1
           FROM   #tab2
           WHERE  rn = (
                      SELECT MAX(rn)
                      FROM   #tab2
                  )
       )
       AND 
       (
           SELECT a - 1
           FROM   #tab2
           WHERE  rn = (
                      SELECT MIN(rn)
                      FROM   #tab2
                  )
       )
--       a           b
------------- -----------
--3           9
--4           0
--5           8

--(3 行受影响)

这是SQL Server的代码，不知道能不能理解哈，但是逻辑都是一样，你理解了我写的那些注释的话自己写出来就没问题了，挺简单的

[碧水幽幽泉]

经过测试和验证，上面两个SQL语句均可实现你要的效果！

[mayanzs]

select * from t where a between (select min(a) from t where b<>0) and (select max(a) from t where b<>0);

[碧水幽幽泉]

你的SQL SERVER代码改成Oracle代码，如下：

select *
  from tab
  where a between 
       (select a + 1
          from (select a,row_number() over(order by rn desc, a) as rn from(select a,a-row_number()over(order by a) rn from tab where b=0))
         where rn = (select max(rn)
		              from(select a,row_number() over(order by rn desc, a) as rn
						     from(select a,a-row_number()over(order by a) rn from tab where b=0) 
						  )  
                     )
       )
       and 
       (select a - 1
          from (select a,row_number() over(order by rn desc, a) as rn
                  from(select a,a-row_number()over(order by a) rn from tab where b=0)
			    )
         where rn = (select min(rn)
					   from(select a,row_number() over(order by rn desc, a) as rn
						      from(select a,a-row_number()over(order by a) rn from tab where b=0) 
						   )  
                     )
       );

[碧水幽幽泉]

脚本还可以再优化一下，如下所示：

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 tab where b=0)
)
select *
  from tab
 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));