用top 和 not in 对读取的数据分页

source: unknnown. if you know, please tell me. thanks!

 

从表中取出第 n 条到第 m 条的记录：
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
(SELECT TOP n-1 id
FROM publish))

依此而得到的分页存储过程：

 

use testDB
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [testDate] [datetime] NOT NULL CONSTRAINT [DF_testTable_testDate]  DEFAULT (getdate()),
    [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [description] [nchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [orderColum] [float] NOT NULL,
 CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--填充一下表，加了3W条记录，倒是不多。
declare @i int
set @i = 1
while @i < 30001
begin
    INSERT INTO testTable([name],[description],[orderColum])
         VALUES('names', 'descriiption', @i * rand())
    set @i = @i +1
end

-- ============================================
-- Author:
-- Create date:
-- Description:    <Description,selectTopN分页方法,>
-- =============================================
if   exists   (select   *   from   dbo.sysobjects  
      where   id   =   object_id(N'[dbo].[sp_SelectTopN]')   and
      OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
 drop   procedure   [dbo].[sp_SelectTopN]  
GO

CREATE PROCEDURE [dbo].[sp_SelectTopN](
    -- Add the parameters for the stored procedure here
    @TableName varchar(200) = 'testTable',    --表名
    @PageSize int = 15,        --页面大小
    @PageIndex int =2    ,    --页面的序号
    --@IsCountNull bit =1,    --返回记录是否为空
    @IsAsc bit = 1    ,    --是否卫升序,升序为1，降序为0
    @OderColumName varchar(200) = null,        --排序字段名
    @KeyID varchar(50) = 'id',        --主键
    @Conditions varchar(500) = null --查询条件
)
AS
    set nocount on

    declare @strSql nvarchar(1000)
    declare @tempstr nvarchar(1000)
    declare @orderstr nvarchar(400)

--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
    if @IsAsc = 1
    begin
        if(@OderColumName is null or @OderColumName = '')
            set @orderstr = ' order by ' + @KeyID + ' asc'
        else
            set @orderstr = ' order by ' + @OderColumName + ' asc'
    end
    else
    begin
        if(@OderColumName is null or @OderColumName = '')
            set @orderstr = ' order by ' + @KeyID + ' desc'
        else
            set @orderstr = ' order by ' + @OderColumName + ' desc'
    end

--查询条件是否添加
    if @Conditions is null
    begin
        set @tempstr = 'select top ' +  str(@PageSize*@PageIndex)  +' ' +@KeyID +' from ' + @TableName + @orderstr;
        set @strSql = 'select top ' +  str(@PageSize)+ '  * from ' + @TableName + ' where ' + @KeyID +' not in ( ' + @tempstr +')' +  @orderstr;
    end
    else
        begin
            set @tempstr = 'select top ' +  str(@PageSize*@PageIndex)  +' ' +@KeyID +' from ' + @TableName+ ' where ' + @Conditions + ' '  + @orderstr;
            set @strSql = 'select top ' +  str(@PageSize)+ '  * from ' +  @TableName + ' where ' + @Conditions + ' and '  + @KeyID +' not in (' + @tempstr +')' +  @orderstr;
        end
    --print @strSql
 select @strSql
    exec sp_executesql @strSql
set nocount off

--测试
DECLARE @return_value int
EXEC    @return_value = [dbo].[sp_SelectTopN]
       @TableName = N'testTable',
       @PageSize = 30,
       @PageIndex = 4,
       @IsAsc = 1,
       @OderColumName = N'id',
       @KeyID = N'id',
       @Conditions = 'id > 50'
SELECT    'Return Value' = @return_value
go

 

 

若欲分页结果集的排序字段出现重复值，而导致用 not in 分页时，出现临界值不能正常找出显示问题时，可以通过创建一个临时表，并在临时表中插入一个唯一序号来实现。如下面的例子中，两表联合，但由于customers中的cid和providers中的pid均为唯一序号字段，可能会出现重复而导致not in时，临界出现问题，所以采用创建临时表和插入新唯一序号字段来实现分页功能。但若原表数据变动，会导致临时表必须重新刷新创建。否测，结果会因为临时表没及时更新而导致结果不一致。

 

select identity(int,1,1) tid, a.* into #temp
--select tid=identity(int,1,1),a.* into #table
from (  select *
  from customers

  union

  select *
  from providers
   )  a

 

select *
from #temp
where tid between 5 and 10

 

select *
from #temp

 

drop table #temp