SQL分页技巧-优快云博客

本文介绍了多种SQL分页方法，包括使用NOT IN、ROW_NUMBER()、存储过程及通过临时表实现分页查询。针对不同场景提供了具体示例，如基于ID的分页、利用ROW_NUMBER()对表记录进行编号等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在博客园上看到的，收集一下。。。

假设一张表T_Person（id为主键）,总共m条信息，要求：每页：n条，页数：page=（m%n）==0？（m/n）：（m/n+1）；

1、把所需数据全部读到前台，然后用写一个方法用for循环读取第n页的信息

2、SQL：not in

存储过程一个参数n:显示数据的第几页

3、ROW_NUMBER

一个根据Id分页：需要有一个连续的id值来查询数据

View Code

select * from
 where id>(n-1)*5 and id<=n*5

这个受数据库增删影响，id很可能不连续导致查询结果不对，为此可以用ROW_NUMBER把整张表先全部查出对其记录进行编号

View Code

//参数:@pagesize每页显示的记录数，@page第几页CREATE 

PROCEDURE Get_personInfo
    @pagesize int,
    @pageIndex int
AS
begin
 select * from
        (SELECT row_number() OVER(ORDER BY id ASC) as _id,* FROM Company) as a 
 where _id >@pagesize*(@pageIndex-1) AND _id=(@pagesize*@pageIndex)
 
END
GO

4、存储过程(这个存储过程中@startID如何根据@pageIndex不同而改变其值，还不是很看得懂）

View Code

ALTER PROCEDURE [dbo].[test]
 @TableName varchar(30),--表名称
 @IDName varchar(20),--表主键名称
 @PageIndex int,--当前页数 
 @PageSize int--每页大小 

AS
IF @PageIndex > 0
BEGIN
set nocount on
    DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)
    SET @PageLowerBound = @PageSize * (@PageIndex-1)
    IF @PageLowerBound<1
     SET @PageLowerBound=1
    SET ROWCOUNT @PageLowerBound
    SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName   
      exec sp_executesql @sql,N'@StartID int output',@StartID output
    SET ROWCOUNT 0
    IF @startId>1
        begin
         set @StartID=@StartID+1
        end      
    SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '
    EXEC(@sql)
 set nocount off
   
END

5、工作用到的，主要通过临时表

View Code

ALTER PROCEDURE [dbo].[pro_getcallReport]
@userid int=-1,
@callRequestNum varchar(50)=null,
@callStatus int=-1,
@callCategory int=-1,
@serviceType int=-1,--toptradeID
@district varchar(200)=null,
@region varchar(200)=null,
@fromCallDate varchar(30)=null,
@toCallDate   varchar(30)=null,
@pageIndex1 int,
@pageSize1 int

AS
BEGIN
Declare @sql1 varchar(8000)
Declare @sql2 varchar(8000)
Declare @sql3 varchar(8000)
Declare @sql4 varchar(8000)
declare @pageIndex int set @pageIndex=@pageIndex1
declare @pageSize int set @pageSize=@pageSize1


DECLARE @StartPoint VARCHAR(10)
DECLARE @EndPoint VARCHAR(10)

SELECT @StartPoint = @PageSize * ( @PageIndex - 1 ) + 1 ,
    @EndPoint = @StartPoint + @PageSize - 1    

set @sql1='
DECLARE @Results TABLE
(
    ID_IDENTITY INT IDENTITY PRIMARY KEY,
    id int,
    callRequestNum varchar(50),
    [status] int,    
    callcategory int,
    callTime datetime,
    serviceType int,
    locationID varchar(50),
    district varchar(200),
    region varchar(200),
    projectManager nvarchar(100)
    
)
'
set @sql2='
insert into @Results
(    id,
    callRequestNum,
    [status],    
    callcategory,
    callTime,
    serviceType,
    locationID,
    district,
    region,
    projectManager
)
 SELECT       tb1.ID,
           tb1.callrequestnum AS callRequestNum,
           tb1.callStatus as status, 
           tb1.callcategory as category,
           tb1.calldatetime AS callTime,            
           tb1.topTradeID as serviceType,
          tb2.LocationNum AS locationID,
          tb2.district,
          tb2.region,
          tb3.name AS projectManager
    FROM call as tb1,shop as tb2,[user] as tb3
    WHERE tb1.shopID=tb2.id and tb1.adduser=tb3.id 
    and tb3.id ='+Convert(varchar,@userid)
    

    if @callRequestNum <>''
        set @sql2=@sql2+' and tb1.callrequestnum='''+@callRequestNum+''''
    if @callStatus <>-1
        set @sql2=@sql2+' and tb1.callstatus='+Convert(varchar,@callStatus)
    if @callCategory<>-1
        set @sql2=@sql2+' and tb1.callcategory='+Convert(varchar,@callCategory)
    if(@serviceType<>-1)
        set @sql2=@sql2+' and tb1.toptradeID='+Convert(varchar,@serviceType)
    if @district <>''
        set @sql2=@sql2+' and tb2.District='''+@district+''''
    if @region <>''
        set @sql2=@sql2+' and tb2.Region='''+@region+''''
    if @fromCallDate <>'' and @toCallDate <>''
        set @sql2=@sql2+' and calldatetime between '''+@fromCallDate+''' and '''+@toCallDate+''''
    else if(@fromCallDate<>'' and @toCallDate ='')
        set @sql2=@sql2+'  and calldatetime >='''+@fromCallDate+''''
    else if(@toCallDate='' and @toCallDate <>'')
        set @sql2=@sql2+'  and calldatetime <='''+@toCallDate+''''
    
    set @sql2=@sql2+' order by tb1.calldatetime desc'
    
        
        
    
SET @sql3 = '
DECLARE @iCount AS int
SELECT @iCount = MAX(ID_IDENTITY) FROM @Results
SELECT 
        iCount = @iCount,
        id,
        callRequestNum,
        [status],    
        callcategory,
        callTime,
        serviceType,
        locationID,
        district,
        region,
        projectManager
FROM 
        @Results 
WHERE 
        ID_IDENTITY BETWEEN '+@StartPoint+' AND '+@EndPoint
--ORDER BY
--convert(smalldatetime,ServiceDate) DESC

  print(@sql1+@sql2+@sql3)
  exec(@sql1+@sql2+@sql3)


END