数据分页处理方法汇总（例子）

分页处理一般有很多方法，临时表是用的最多的一种，然而还有其他方法：

1.SET ROWCOUNT ON
SELECT TOP (PAGE+1)*SIZE TABLE.* FROM TABLE WHERE ORDER BY COLUMN
SET ROWCOUNT ON SIZE
SELECT TABLE.* FROM TABLE WHERE ORDER BY COLUMN
特点：读出来的每页数据逆向排列

2.TOP （和第一种方法原理一样）
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc

3.创建identity列(或者先创建带identity列的临时表，然后就很好处理)
select identity(int) id0,* into #temp from tablename

取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m

Example(方法2)：

exec
HRPE_PeriodResultQueryByCond
@KaoHePlan = N'01',
@BeginPeriod = N'1',
@EndPeriod = N'2',
@WhereFilter = N'1=1 and HRPEKHWDDF_DXXZ =''1'' And HRPEKHWDDF_BKHDX = ''2dde7c1f-6ab2-4d5d-9bb9-2ad72ddbc99e''', @Size = 30,
@Page = 1,
@psFlag = N'',
@psCompanyID = N'',
@psQueryID = N'HRPE_PeriodResultQuery152677699063288832',
@psFormatNo = N'01'

1 exec
2 HRPE_PeriodResultQueryByCond
3 @KaoHePlan = N ' 01 ' ,
4 @BeginPeriod = N ' 1 ' ,
5 @EndPeriod = N ' 2 ' ,
6 @WhereFilter = N ' 1=1 and HRPEKHWDDF_DXXZ = '' 1 '' And HRPEKHWDDF_BKHDX = '' 2dde7c1f-6ab2-4d5d-9bb9-2ad72ddbc99e ''' , @Size = 30 ,
7 @Page = 1 ,
8 @psFlag = N '' ,
9 @psCompanyID = N '' ,
10 @psQueryID = N ' HRPE_PeriodResultQuery152677699063288832 ' ,
11 @psFormatNo = N ' 01 '
12
13 SELECT TOP 30 D. * FROM
14 ( SELECT TOP 30 HRPEKHWDDF_BKHDX = CASE A.HRPEKHWDDF_DXXZ -- (@page+1)*@Size
15 WHEN ' 1 ' THEN ( SELECT HRHYZD_ZGXM FROM HRHYZD WHERE HRHYZD_YGNM = A.HRPEKHWDDF_BKHDX)
16 WHEN ' 2 ' THEN ( SELECT HRORBMZD_MC FROM HRORBMZD WHERE HRORBMZD_NM = A.HRPEKHWDDF_BKHDX)
17 WHEN ' 3 ' THEN ( SELECT LSBZDW_DWMC FROM LSBZDW WHERE LSBZDW_DWBH = A.HRPEKHWDDF_BKHDX)
18 END ,
19 ( SELECT HRPEKHQJ_KSRQ + ' -- ' + HRPEKHQJ_JSRQ FROM HRPEKHQJ WHERE HRPEKHQJ_QJNM = A.HRPEKHWDDF_SSFA) AS HRPEKHQJ_KHQJ,
20 ( SELECT HRPEKHDXDF_TZDF FROM HRPEKHDXDF C WHERE C.HRPEKHDXDF_BKHDX = A.HRPEKHWDDF_BKHDX AND HRPEKHDXDF_SSFA = A.HRPEKHWDDF_SSFA ) AS HRPEKHDXDF_HZDF,
21 ( SELECT HRPEJXDJDY_DJ FROM HRPEJXDJDY WHERE HRPEJXDJDY_DYFA = ' 01 ' AND B.HRPEKHDXDF_TZDF > HRPEJXDJDY_DFXX AND HRPEJXDJDY_DFSX > B.HRPEKHDXDF_TZDF OR B.HRPEKHDXDF_TZDF = HRPEJXDJDY_DFXX) AS HRPEKHWDDF_LEVEL ,
22 SUM ( CASE HRPEKHWDDF_KHWD WHEN ' 1 ' THEN HRPEKHWDDF_TZDF ELSE 0 END ) [ 1 ] ,
23 SUM ( CASE HRPEKHWDDF_KHWD WHEN ' 2 ' THEN HRPEKHWDDF_TZDF ELSE 0 END ) [ 2 ] ,
24 SUM ( CASE HRPEKHWDDF_KHWD WHEN ' 3 ' THEN HRPEKHWDDF_TZDF ELSE 0 END ) [ 3 ]
25 FROM HRPEKHWDDF A INNER JOIN HRPEKHDXDF B ON B.HRPEKHDXDF_BKHDX = A.HRPEKHWDDF_BKHDX AND B.HRPEKHDXDF_SSFA = A.HRPEKHWDDF_SSFA AND A.HRPEKHWDDF_DXXZ = B.HRPEKHDXDF_BKHDXXZ
26 WHERE 1 = 1
27 and HRPEKHWDDF_DXXZ = ' 1 '
28 And HRPEKHWDDF_BKHDX = ' 2dde7c1f-6ab2-4d5d-9bb9-2ad72ddbc99e '
29 AND A.HRPEKHWDDF_SSFA IN
30 ( SELECT HRPESSFAZD_SSFANM FROM HRPESSFAZD WHERE HRPESSFAZD_KHFA = ' 01 '
31 AND HRPESSFAZD_STAT = ' 1 '
32 AND HRPESSFAZD_KHQJ IN
33 ( SELECT HRPEKHQJ_QJNM FROM HRPEKHQJ
34 WHERE HRPEKHQJ_KSRQ >= ' 20070101 '
35 AND HRPEKHQJ_JSRQ <= ' 20070228 '
36 AND HRPEKHQJ_ZT = ' 1 '
37 AND HRPEKHQJ_LXNM = ( SELECT HRPEKHFAZD_KHLX FROM HRPEKHFAZD WHERE HRPEKHFAZD_FANM = ' 01 ' ) ))
38 GROUP BY B.HRPEKHDXDF_TZDF,HRPEKHWDDF_BKHDX,HRPEKHWDDF_DXXZ,HRPEKHWDDF_SSFA
39 Order By HRPEKHWDDF_BKHDX DESC ,HRPEKHQJ_KHQJ DESC ) AS D -- 子查询结束
40 ORDER BY HRPEKHWDDF_BKHDX,HRPEKHQJ_KHQJ
41

SELECT TOP 30 D.* FROM
(SELECT TOP 30 HRPEKHWDDF_BKHDX = CASE A.HRPEKHWDDF_DXXZ --(@page+1)*@Size
WHEN '1' THEN (SELECT HRHYZD_ZGXM FROM HRHYZD WHERE HRHYZD_YGNM = A.HRPEKHWDDF_BKHDX)
WHEN '2' THEN (SELECT HRORBMZD_MC FROM HRORBMZD WHERE HRORBMZD_NM = A.HRPEKHWDDF_BKHDX)
WHEN '3' THEN (SELECT LSBZDW_DWMC FROM LSBZDW WHERE LSBZDW_DWBH = A.HRPEKHWDDF_BKHDX)
END ,
(SELECT HRPEKHQJ_KSRQ+'--'+HRPEKHQJ_JSRQ FROM HRPEKHQJ WHERE HRPEKHQJ_QJNM = A.HRPEKHWDDF_SSFA) AS HRPEKHQJ_KHQJ,
(SELECT HRPEKHDXDF_TZDF FROM HRPEKHDXDF C WHERE C.HRPEKHDXDF_BKHDX = A.HRPEKHWDDF_BKHDX AND HRPEKHDXDF_SSFA = A.HRPEKHWDDF_SSFA ) AS HRPEKHDXDF_HZDF,
(SELECT HRPEJXDJDY_DJ FROM HRPEJXDJDY WHERE HRPEJXDJDY_DYFA ='01' AND B.HRPEKHDXDF_TZDF>HRPEJXDJDY_DFXX AND HRPEJXDJDY_DFSX >B.HRPEKHDXDF_TZDF OR B.HRPEKHDXDF_TZDF=HRPEJXDJDY_DFXX) AS HRPEKHWDDF_LEVEL ,
SUM(CASE HRPEKHWDDF_KHWD WHEN '1' THEN HRPEKHWDDF_TZDF ELSE 0 END)[1],
SUM(CASE HRPEKHWDDF_KHWD WHEN '2' THEN HRPEKHWDDF_TZDF ELSE 0 END)[2],
SUM(CASE HRPEKHWDDF_KHWD WHEN '3' THEN HRPEKHWDDF_TZDF ELSE 0 END)[3]
FROM HRPEKHWDDF A INNER JOIN HRPEKHDXDF B ON B.HRPEKHDXDF_BKHDX = A.HRPEKHWDDF_BKHDX AND B.HRPEKHDXDF_SSFA = A.HRPEKHWDDF_SSFA AND A.HRPEKHWDDF_DXXZ = B.HRPEKHDXDF_BKHDXXZ
WHERE 1=1
and HRPEKHWDDF_DXXZ ='1'
And HRPEKHWDDF_BKHDX = '2dde7c1f-6ab2-4d5d-9bb9-2ad72ddbc99e'
AND A.HRPEKHWDDF_SSFA IN
(SELECT HRPESSFAZD_SSFANM FROM HRPESSFAZD WHERE HRPESSFAZD_KHFA = '01'
AND HRPESSFAZD_STAT = '1'
AND HRPESSFAZD_KHQJ IN
(SELECT HRPEKHQJ_QJNM FROM HRPEKHQJ
WHERE HRPEKHQJ_KSRQ >= '20070101'
AND HRPEKHQJ_JSRQ <= '20070228'
AND HRPEKHQJ_ZT = '1'
AND HRPEKHQJ_LXNM = (SELECT HRPEKHFAZD_KHLX FROM HRPEKHFAZD WHERE HRPEKHFAZD_FANM = '01') ))
GROUP BY B.HRPEKHDXDF_TZDF,HRPEKHWDDF_BKHDX,HRPEKHWDDF_DXXZ,HRPEKHWDDF_SSFA
Order By HRPEKHWDDF_BKHDX DESC,HRPEKHQJ_KHQJ DESC) AS D --子查询结束
ORDER BY HRPEKHWDDF_BKHDX,HRPEKHQJ_KHQJ

结果：
黄勇 20070101--20070131 61.0 6级 70.0 80.0 240.0
黄勇 20070201--20070228 58.0 5级 0.0 0.0 63.0

陆续添加 ！！！