有如下資料：
ID 順序 新增列
1812300146 20 2
1812300146 19 2
1812300256 18 2
1812300256 17 2
1812300146 16 1
1812300146 15 1
1812300257 14 1
1812300257 13 1
1812300257 12 1
1812300258 11 2
1812300258 10 2
1812300121 9 2
1812300258 8 1
1812300258 7 1
1812300256 6 1
1812300256 5 1
1812300121 4 1
1812300121 3 1
1812300121 2 1
1812300121 1 1
判斷某一ID是否是連續的，如果是連續的，則對應的新增列為同一數值，若出現斷層，從新出現該ID，則將新增列+1，如1~4是1812300121 ，新增列為1，到9又出現1812300121，則新增列為2，這樣要如何判斷？請指教！
uj5u.com熱心網友回復：
with tab as (
select 1 id, 1 ord from dual union all
select 1 id, 2 ord from dual union all
select 2 id, 3 ord from dual union all
select 2 id, 4 ord from dual union all
select 1 id, 5 ord from dual union all
select 1 id, 6 ord from dual union all
select 3 id, 7 ord from dual union all
select 1 id, 8 ord from dual union all
select 3 id, 9 ord from dual
)
,tab2 as (
select t1.*,
decode(lag(t1.id) over(order by t1.ord), t1.id, 0, 1) lg
from tab t1
)
select t1.id,
t1.ord,
sum(t1.lg) over(partition by t1.id order by t1.ord) new_col
from tab2 t1
order by t1.ord
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/55500.html
標籤：開發
上一篇：求助，plsql操作相關
下一篇：oracle 性能問題