根据biillno进行分组后根据LV排序后再进行前后比对,求出a1连续递增、连续递减、连续不减少的最大值~然后在把比对出来的值插入另一张表中。
解决方案 »
- Oracle中instr的方法,有没有替代的谢谢
- 关于oracle下载的问题
- sybase触发器转成oracle~~大家帮忙~~
- 求教关于多行插入的问题!!!
- 在Oracle中有查看它所在服务器的CPU、内存、磁盘信息吗?
- sql="select * from ck where hw_name like '"&hao&"' " 请问我想加上个通配符'*',不知道应该在哪里加?谢谢大家!
- 关于oracele的安装问题
- *****ORACLE如何自动启动ORACLE数据库实例?*****在线等待!
- 函数中如何执行字符串?
- 请各位前辈推荐几本比较好一点的oracle管理和开发方面的书
- 求助 用kettle进行json输出
- 谁有Oracle的Support Identifier?急用下载Oracle 11.2.0.4补丁。
@nayi_224
(select 1 id, t1.a1, 1 lv from temp01 t1 union all
select 1 id, t1.a2, 2 from temp01 t1 union all
select 1 id, t1.a3, 3 from temp01 t1 union all
select 1 id, t1.a4, 4 from temp01 t1 union all
select 1 id, t1.a5, 5 from temp01 t1 union all
select 2 id, t1.a2, 2 from temp01 t1 union all
select 2 id, t1.a3, 3 from temp01 t1 union all
select 2 id, t1.a4, 4 from temp01 t1 union all
select 1 id, t1.a6, 6 from temp01 t1 )
,tab2 as (
select t1.*,
case
when lead(t1.a1) over(partition by id order by lv) > a1 and
nvl(lag(t1.a1) over(partition by id order by lv), a1 + 1) > a1 then
1
when lag(t1.a1) over(partition by id order by lv) >= a1 then
1
else
0
end fin
from tab t1)
, tab3 as (
select t1.*, sum(t1.fin) over(partition by id order by lv) su from tab2 t1
), tab4 as (
select t1.*,
count(1) over(partition by id, su) part_1_cot
from tab3 t1
)
select t1.*,
max(t1.part_1_cot) over(partition by id) part_2_cot
from tab4 t1;