根據biillno進行分組后根據LV排序后再進行前后比對，求出a1連續遞增、連續遞減、連續不減少的最大值~然后在把比對出來的值插入另一張表中。
uj5u.com熱心網友回復：
@yaiger@nayi_224
uj5u.com熱心網友回復：
你可以參考https://bbs.csdn.net/topics/392420616nayi_224的回答，自己嘗試寫一下uj5u.com熱心網友回復：
最好把表結構和測驗資料的腳本都貼出來，便于大家測驗uj5u.com熱心網友回復：
加個partition就行了
with tab as
(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
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62108.html
標籤：開發
下一篇：求助SQL陳述句高手