資料庫中有如下的資料
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C01
A01 B01 C02
A01 B01 C03
根據三個欄位設定頁數，第一頁有三條資料，第二頁以后有5條資料。
當key發生變化時，頁數重1開始加算。
想要如下的結果資料
A01 B01 C01 1
A01 B01 C01 1
A01 B01 C01 1
A01 B01 C01 2
A01 B01 C01 2
A01 B01 C01 2
A01 B01 C01 2
A01 B01 C01 2
A01 B01 C01 3
A01 B01 C01 3
A01 B01 C01 3
A01 B01 C01 3
A01 B01 C01 3
A01 B01 C01 4
A01 B01 C02 1
A01 B01 C03 1
求教SQL文如何實作！！！！
uj5u.com熱心網友回復：
第二頁的5，是怎么來的？uj5u.com熱心網友回復：
第一頁3，第二頁5是固定值。
uj5u.com熱心網友回復：
with t as(
select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C02' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C03' as c3 from dual
),
t1 as(
select c1,c2,c3 from t where rownum<3
union all
select c1,c2,c3 from t
)
select c1,c2,c3,ym from
(select c1,c2,c3,trunc((rm+4)/5) ym,rownum rm1 from
(select c1,c2,c3,row_number() over(partition by c3 order by c3) rm
from t1))
where rm1>=3
uj5u.com熱心網友回復：
資料庫中件數是不固定的啊。uj5u.com熱心網友回復：
啥意思？
uj5u.com熱心網友回復：
with t as(
select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C02' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C03' as c3 from dual
),
t1 as(select t.*,row_number() over(partition by c1,c2,c3 order by c1,c2,c3) rm from t)
select t1.*,1,xh from t1 where rm<=3 union all select t1.*,trunc((rm+1)/5) from t1 where rm>3 order by 1,2,3,4
uj5u.com熱心網友回復：
我列出來的資料只是舉例說明。真實資料庫會有很多條的。不固定件數。uj5u.com熱心網友回復：
有很多條資料怎么了，會有什么問題嗎？
uj5u.com熱心網友回復：
樓上這思路不錯，但是建議修改下演算法，沒必要在加select c1,c2,c3 from t where rownum<3with t as(
select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C01' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C02' as c3 from dual
union all select 'A01' as c1,'B01' as c2,'C03' as c3 from dual
)
select c1,c2,c3,ym,rm from
(select c1,c2,c3,(case when rm>3 then trunc((rm-4)/5+2) else 1 end) ym,rm from
(select c1,c2,c3,row_number() over(partition by c3 order by c3) rm
from t))
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/117703.html
標籤：開發
上一篇：奇葩問題
下一篇：sql查詢