col01 col02
0 1
0 2
1 3
1 4
1 5
0 ... 6...要求,select 出col01是连续3个1的SQL语句,拜托各位高手了
倒是写了个函数来实现,但是想直接一条SQL语句能实现就完美了:)
0 1
0 2
1 3
1 4
1 5
0 ... 6...要求,select 出col01是连续3个1的SQL语句,拜托各位高手了
倒是写了个函数来实现,但是想直接一条SQL语句能实现就完美了:)
解决方案 »
- select操作 但是数据量太大 要等很长时间 能中途取消么
- !!在线急等高手!!多表连接,字段为空,结果集为空问题!
- 有一张记录用户登录信息的表,现想统计2小时内登录次数超过N次的用户,如何用sql实现
- 请教此表的情况是否被锁定。
- oracle 空闲队列
- Oracle Data Access Components for Oracle Server 11.2.0.3.0只能安装在现有 Oracle Databas
- 数据查询---IN 导致查询过慢,怎样解决?
- 新手请问一个存储过程的问题,在线急等!
- 求救各位Oracle高手!!
- sql改写
- 关于dbms_lob.substr的问题
- BAT中调用SQL文件,然后再SQL文件中调用存储过程
with t as(select 0 col01,1 col02 from dual
union all select 0,2 from dual
union all select 1,3 from dual
union all select 1,4 from dual
union all select 1,5 from dual
union all select 0,6 from dual
)
select col01,col02 from(
select t.*,max(level)over(partition by connect_by_root col02)ma
from t
start with col01=1
connect by prior col02=col02-1 and col01=1)
where ma>=3
select t.*,connect_by_root col02
from t
start with col01=1
connect by prior col02=col02-1 and col01=1
树形查询。从col01=1的开始,按col02顺序向下遍历。connect_by_root col02是根节点的col02的值。10g以上才有第二步
select t.*,max(level)over(partition by connect_by_root col02)ma
from t
start with col01=1
connect by prior col02=col02-1 and col01=1
加个分析函数,求出按根节点col02值分组,最大的level值(树形查询的等级,不懂可以google下)。这个level值实际上就是连续col01为1的数量第三步
过滤出ma为3的记录
from t a,t b
where b.col01=1
and not exists(
select 1 from t where (col02 between a.col02 and b.col02 or col02 between b.col02 and a.col02)
and col01=0)
and a.col01=1
group by a.col01,a.col02
having count(1)>=3
order by 2这样也可以,性能可能差点
SQL> with t as(select 0 col01,1 col02 from dual
2 union all select 0,2 from dual
3 union all select 1,3 from dual
4 union all select 1,4 from dual
5 union all select 1,5 from dual
6 union all select 0,6 from dual
7 union all select 0,7 from dual
8 union all select 1,8 from dual
9 union all select 1,9 from dual
10 )
11 select col01, col02
12 from (select col01, col02,
13 count(1) over(partition by col01,my_rows) counts
14 from (select rownum - row_number() over(partition by col01 order by rownum) my_rows,
15 t.*
16 from t) a) b
17 where col01 = 1
18 and counts = 3;
COL01 COL02
---------- ----------
1 3
1 4
1 5