用于分页显示的SQL文

原址 http://technet.microsoft.com/ja-jp/library/cc411406.aspx

 

看了一篇SQL2005关于Transact-SQL的强化技能的介绍，觉得很有用处，简单总结了一下。

 

（一）数据库做成

USE tempdb – 或者新建一个DB

CREATE TABLE SpeakerStats

(

  speaker        VARCHAR(10) NOT NULL PRIMARY KEY,

  track          VARCHAR(10) NOT NULL,

  score          INT         NOT NULL,

  pctfilledevals INT         NOT NULL,

  numsessions    INT         NOT NULL

)

 

SET NOCOUNT ON

INSERT INTO SpeakerStats VALUES('Dan',     'Sys', 3, 22, 4)

INSERT INTO SpeakerStats VALUES('Ron',     'Dev', 9, 30, 3)

INSERT INTO SpeakerStats VALUES('Kathy',   'Sys', 8, 27, 2)

INSERT INTO SpeakerStats VALUES('Suzanne', 'DB',  9, 30, 3)

INSERT INTO SpeakerStats VALUES('Joe',     'Dev', 6, 20, 2)

INSERT INTO SpeakerStats VALUES('Robert',  'Dev', 6, 28, 2)

INSERT INTO SpeakerStats VALUES('Mike',    'DB',  8, 20, 3)

INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)

INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)

INSERT INTO SpeakerStats VALUES('Brian',   'Sys', 7, 22, 3)

INSERT INTO SpeakerStats VALUES('Kevin',   'DB',  7, 25, 4)

 

（二）

SQL2005下的SQL语句（1）和2000下的（2）执行结果一致

（1）

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,

                           numsessions DESC, speaker) AS rownum,

  speaker, track, score, pctfilledevals, numsessions

FROM SpeakerStats

ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

 

（2）

SELECT

  (SELECT COUNT(*)

   FROM SpeakerStats AS S2

   WHERE S2.score > S1.score

     OR (S2.score = S1.score

         AND S2.pctfilledevals > S1.pctfilledevals)

     OR (S2.score = S1.score

         AND S2.pctfilledevals = S1.pctfilledevals

         AND S2.numsessions > S1.numsessions)

     OR (S2.score = S1.score

         AND S2.pctfilledevals = S1.pctfilledevals

         AND S2.numsessions = S1.numsessions

         AND S2.speaker < S1.speaker)) + 1 AS rownum,

  speaker, track, score, pctfilledevals, numsessions

FROM SpeakerStats AS S1

ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

 

 

（三）

只显示固定范围的行号

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,

        speaker, track, score

      FROM SpeakerStats) AS D

WHERE rownum BETWEEN 4 AND 6

ORDER BY score DESC, speaker

 

rownum speaker    track      score

------ ---------- ---------- -----------

4      Kathy      Sys        8

5      Michele    Sys        8

6      Mike       DB         8

 

 

（四）

 @pagenum  ：表示当前页数

 @pagesize ：表示每一页最大显示行数

DECLARE @pagenum AS INT, @pagesize AS INT

SET @pagenum = 2      

SET @pagesize = 3

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,

        speaker, track, score

      FROM SpeakerStats) AS D

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY score DESC, speaker

 

rownum speaker    track      score

------ ---------- ---------- -----------

4      Kathy      Sys        8

5      Michele    Sys        8

6      Mike       DB         8

（结果共11行，每页三行，第二页共显示3条，即4-6）

 

（五）

每一行经过计算之后得到的行号存入TABLE，用该行号索引数据

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *

INTO #SpeakerStatsRN

FROM SpeakerStats

CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

 

 

可以用以下SQL文，简单执行一下，看看效果，^_^

DECLARE @pagenum AS INT, @pagesize AS INT

SET @pagenum = 2

SET @pagesize = 3

 

SELECT rownum, speaker, track, score

FROM #SpeakerStatsRN

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY score DESC, speaker