Originally posted address: http://community.9cbs.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', '2004-12-11'Union All SELECT 2,' B ',' 2004- 12-11 'ELON All SELECT 3,' C ',' 2004-12-11'Union All SELECT 4, 'A', '2004-12-12'Union All SELECT 5,' C ',' 2004-12- 13'Union all SELECT 6, 'C', '2004-12-13'Union All SELECT 7,' A ',' 2004-12-14'Union All SELECT 8, 'A', '2004-12-15' Union All Select 9, 'B', '2004-12-16', '2004-12-17'Union All SELECT 11,' A ',' 2004-12-17'go
/ * - Paging processing requirements
5 records per page: Class C class 2 B 1 Class C class 2 data order, uptime desc, grade = c> b> a, when id DESC is insufficient, by its subsequent class - * /
- Sub-processing stored procedure CREATE PROC P_SPLIT @ CREATE PROC P_SPLIT @ CRETPAGE INT = 1, - The current page to display @PageSize Int = 5 - The size of each page (if this is adjusted, the stored procedure is also required Do the corresponding modification, namely: Case grade when 'c' 2 When 'b' Ten 1 When 'a' Ten 2 End section, this control Each class / per page record asset nocount online @ currentpage = @ currentpage * @ PageSizset RowCount @currentpageselect * Into #t from TB Aorder by ((Select Count (*) from Tb Where Grade = a.grade and (uptime> a.uptime or uptime = a.uptime and id> = a.id)) 1) / Case grade when 'c' 2 when 'b' Then 1 by 'a' Then 2 End, Case grade when 'c' Then 1 When 'b' Then 2 When 'a' Ten 3 end, ID descif @ currentpage> @pagesizebegin set @ currentpage = @ currentpage- @ pagesize set rowcount @currentpage delete from #tendselect * from #torder by case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end, uptime desc , id descog - call exec p_split 1exec p_split 2exec p_split 3GO
- Delete Table Table TBDROP Proc P_SPLIT
/*--Test Results
ID grade uptime ------------------------------------------ 6 C 2004-12-13 00: 00: 00.0005 C 2004-12-13 00: 00: 00.00010 B 2004-12-17 00: 00: 00.00011 A 2004-12-17 00: 00: 00: A 2004-12-15 00: 00: 00.000
ID grade uptime ------------------------------------------- 3 C 2004-12-11 00: 00: 00.0009 B 2004-12-16 00: 00: 00.0002 B 2004-12-11 00: 00: 00.0007 A 2004-12-14 00: 00: 00.0004 A 2004-12-12 00: 00: 00.000
