Special paging Processing

Original post address:
Http://community.csdn.net/Expert/topic/3662/3662135.xml? Temp =. 4289972.

-- Test Data
Create Table Tb (ID int primary key, grade varchar (10), uptime datetime)
Insert TB select 1, 'A', '2017-12-11'
Union all select 2, 'B', '2017-12-11'
Union all select 3, 'C', '2017-12-11'
Union all select 4, 'A', '2017-12-12'
Union all select 5, 'C', '2017-12-13'
Union all select 6, 'C', '2017-12-13'
Union all select 7, 'A', '2017-12-14'
Union all Select 8, 'A', '2017-12-15'
Union all select 9, 'B', '2017-12-16'
Union all select 10, 'B', '2017-12-17'
Union all select 11, 'A', '2017-12-17'
Go

/* -- Paging processing requirements

Five records per page: Class C, 2 records, Class B, 1 record, category A, and 2 records
Data Sequence, uptime DESC, grade = C> B> A, Id DESC
When a category is insufficient, it is supplemented by its subsequent classes.
--*/

-- Stored procedure for paging
Create proc p_split
@ Currentpage Int = 1, -- the current page to be displayed
@ Pagesize Int = 5 -- size of each page (if this is adjusted, the sorting process also needs to be modified in the stored procedure, namely: case grade When 'C' then 2 when 'B' then 1 when 'A' then 2 end section, which controls the number of records per category/page
Set nocount on
Set @ currentpage = @ currentpage * @ pagesize
Set rowcount @ currentpage
Select * into # T from TB
Order by (select count (*) from TB where grade =. grade and (uptime>. uptime or uptime =. uptime and ID> =. ID)-1)
/Case grade When 'C' then 2 when 'B' then 1 when 'A' then 2 end
, Case grade When 'C' then 1 when 'B' then 2 when 'A' then 3 end, Id DESC
If @ currentpage> @ pagesize
Begin
Set @ currentpage = @ currentpage-@ pagesize
Set rowcount @ currentpage
Delete from # T
End
Select * from # T
Order by case grade When 'C' then 1 when 'B' then 2 when 'A' then 3 end
, Uptime DESC, Id DESC
Go

-- Call
Exec p_split 1
Exec p_split 2
Exec p_split 3
Go

-- Delete test
Drop table TB
Drop proc p_split

/* -- Test Result

Id grade uptime
----------------------------------------------
6 c 2004-12-13 00:00:00. 000
5 C 2004-12-13 00:00:00. 000
10 B 00:00:00. 000
11 A 00:00:00. 000
8 A 2004-12-15 00:00:00. 000

Id grade uptime
----------------------------------------------
3 C 2004-12-11 00:00:00. 000
9 B 00:00:00. 000
2 B 00:00:00. 000
7 A 2004-12-14 00:00:00. 000
4 A 2004-12-12 00:00:00. 000

Id grade uptime
----------------------------------------------
1 A 2004-12-11 00:00:00. 000
--*/