数据分页处理方法汇总（例子）
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'
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
陆续添加 ！！！
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
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
陆续添加 ！！！
