SQLServer分页整理专题

MSSQL分页查询专题整理篇

                                                                   ［By [e-mail]］

现在我用三种情况进行测试

一、 带主键ID的表作分页查询

二、 不带主键ID的表作分页查询

三、 带主键ID但是通过其他字段作分页查询

测试环境：

创建一个表结构如下：

表1. 带主键ID的表结构

CREATE TABLE [dbo].[table1](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [pass] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

这样的分页实现方法

单条SQL语句实现一

SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
          (
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
          )
ORDER BY id

原理：将那些已经查询过的记录排除出去即可。即ID查询过的记录直接排出去。

实际调用：

第一页：

SELECT TOP 10 *

FROM table1

WHERE id NOT IN

          (

          SELECT TOP 0 id FROM table1 ORDER BY id desc

          )

ORDER BY id desc

第二页：

SELECT TOP 10 *

FROM table1

WHERE id NOT IN

          (

          SELECT TOP 10 id FROM table1 ORDER BY id desc

          )

ORDER BY id desc

程序调用也是非常方便的。直接输入两个参数即：每页大小、第几页

第一页是传入1

 

单条SQL语句实现二

SELECT TOP 页大小 *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0) 
          FROM 
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id

这里面是通过ID走升序。当然也可以走降序。

降序的SQL语句：

SELECT TOP 页大小 *
FROM table1
WHERE id <
          (
          SELECT ISNULL(MIN(id),0) 
          FROM 
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id DESC
                ) A
          )
ORDER BY id DESC

第一种情况的实际的例子：

第一页的SQL语句

SELECT TOP 10 *

FROM table1

WHERE id >

          (

          SELECT ISNULL(MAX(id),0)

          FROM

                (

                SELECT TOP 0 id FROM table1 ORDER BY id

                ) A

          )

ORDER BY id

第二页的SQL语句：

SELECT TOP 10 *

FROM table1

WHERE id >

          (

          SELECT ISNULL(MAX(id),0)

          FROM

                (

                SELECT TOP 10 id FROM table1 ORDER BY id

                ) A

          )

ORDER BY id

第二种情况的实际SQL语句：

第一页的SQL语句

SELECT TOP 10 * FROM table1 order by id desc （注意区别）

第二页的SQL语句：

SELECT TOP 10 *

FROM table1

WHERE id <

          (

          SELECT ISNULL(MIN(id),0)

          FROM

                (

                SELECT TOP 10 id FROM table1 ORDER BY id DESC

                ) A

          )

ORDER BY id DESC

 

 

 

表2. 不带主键ID的表结构

如果一个表里面有1KW条记录。而且没有建主键。这种情况我们应当如何实现分页效果呢？

表结构：

CREATE TABLE [dbo].[table1](

    [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [pass] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

这种情况往往是因为有的时候可能业务需要调整排序字段的时候就可能需要用到这种处理方法

解决办法：

1、  将记录导入到一个中间临时表中去。这个中间临时表里面有主键自增长ID

2、   对中间表进行排序处理。

说明：这种情况只能处理数据据不是很高的表。比如一W条记录的时候应该没问题的。

实际操作：

--参数说明

--pageindex 表示当前是第几页 比如说第一页1 第二页 2

--pagesize  表示每页显示的大小 比如10 表示每页显示10条记录

create procedure page_tmp(@pageindex int,@pagesize int)

AS

DECLARE @pagelowerbound int

DECLARE @pageupperbound int

set @pagelowerbound = (@pageindex-1) * @pagesize

set @pageupperbound = @pagelowerbound + @pagesize + 1

create table #tmp(

    id int identity(1,1) not null,

    names varchar(255)

)

INSERT INTO #tmp(names) select name from table1

select a.*

from table1 a(nolock),#tmp T

where a.name = T.names

and T.id > @pagelowerbound and T.id < @pageupperbound

order by T.id

GO

调用实例：

exec page_tmp 4,2

附上：PHP里面通过ODBC方式调用存储过程方法

odbc_exec  封装一段调用存储过程的SQL命令！

 

表3. 带主键ID，但是排序的时候不按照主键ID排序的的表结构

如果一个表里面有1KW条记录。而且有主键但是不是按照主键进行排序的。这种情况经常见于论坛里面

有的时候按照帖子回复数进行排序。而不是按照ID进行排序处理。

表结构：

CREATE TABLE [dbo].[table1](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [pass] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [nums] [bigint] NULL

) ON [PRIMARY]

现在是按照字段nums进行排序处理的。

我的记录内容如下：

select * from  table1 order by nums asc;

截处图看看

如何实现分页效果？

实际的SQL语句：

create procedure page_tmp2(@pageindex int,@pagesize int)

AS

DECLARE @pagelowerbound int

DECLARE @pageupperbound int

set @pagelowerbound = (@pageindex-1) * @pagesize

set @pageupperbound = @pagelowerbound + @pagesize + 1

create table #tmp(

    id int identity(1,1) not null,

    cid int

)

INSERT INTO #tmp(cid) select id from table1 order by nums desc

select a.*

from table1 a(nolock),#tmp T

where a.id = T.cid

and T.id > @pagelowerbound and T.id < @pageupperbound

order by T.id

GO

参数说明：

Pageindex  表示当前是第几页 1 2 3 分别表示第一页、第二页、第三页

Pagesize   表示每页显示条数

调用实例：

第一页 exec page_tmp2 1,2

第二页 exec page_tmp2 2,2

 

OK！至此为止SQLServer或ACCESS数据库如果想要实现分页的话就可以考虑使用上面介绍的几种办法。可以写存储过程也可以写单条SQL语句。

                                              时间：2009-12-16