sql - How to increment with limited value

Question

Hello everyone i am Working on table in with i have around 2million records and i want to add two column PageNo and serialNo based on acc_no

Eg:-

acc_no PageNo SerialNo
abc1   0001   0001
cbf2   0001   0002
.
.
zcd6   0001   9999 

Once SerialNo Reach to 9999 then PageNo Start With 0002

eg:- 
acc_no PageNo SerialNo
abc1   0002   0001
cbf2   0002   0002
.
.
zcd6   0002   9999

and So on I have tried

Select right('0000'+cast(ROW_NUMBER() over(order by acc_no) as varchar(100)),4) PageNo,acc_no from [table Name] 

But i am not getting how to do Need help

标签： sqlsql-serversql-server-2012

Answer 1

用这个

select acc_no, ((rno-1) /9999) +1 pageNo,  ((rno-1) % 9999) + 1 SerialNo
from (
    SELECT acc_no , (ROW_NUMBER() over(order by acc_no )) rno
    FROM [table Name]
) t