数据库中有如下的数据
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文如何实现!!!!
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文如何实现!!!!
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
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
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))