vcno period
vc001 1
vc001 2
vc001 5
vc001 6
vc001 7
vc002 4
vc004 2
vc002 6
vc005 11
資料如上 ， 取出period連續出現最大期次？
結果為
vc001 3
vc002 1
vc004 1
vc005 1
uj5u.com熱心網友回復：
vc001 5vc001 6
vc001 7
這是算是3次？
那 vc002 的1次是怎么算出來的？
uj5u.com熱心網友回復：
with t(v,p) as(select 'vc001',1 from dual union select 'vc001',2 from dual union select 'vc001',5 from dual unionselect 'vc001',6 from dual union select 'vc001',7 from dual union select 'vc002',4 from dual union
select 'vc004',2 from dual union select 'vc002',6 from dual union select 'vc005',11 from dual)
select v, max(cnt)
from (select v, pr, count(*) cnt
from (select v,
p,
p - row_number() over(partition by v order by p) pr
from t)
group by v, pr)
group by v
order by v
uj5u.com熱心網友回復：
with t as(select 'vc001' t1,1 p1 from dual union select 'vc001',2 from dual union select 'vc001',5 from dual unionselect 'vc001',6 from dual union select 'vc001',7 from dual union select 'vc002',4 from dual union
select 'vc004',2 from dual union select 'vc002',6 from dual union select 'vc005',11 from dual)
select ttt.t1,max(cn) from (select tt.t1,count(*) cn from (SELECT t1,p1-row_number() over(partition by t1 order by p1) rn from t) tt
group by tt.t1,tt.rn) ttt group by ttt.t1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/113564.html
標籤：開發
上一篇：關于oracle升級的問題
下一篇：oracle資料庫查詢