SQL 分页，又见分页。

最新推荐文章于 2024-08-09 09:47:03 发布

LightColors

最新推荐文章于 2024-08-09 09:47:03 发布

阅读量480

点赞数

分类专栏：其他技术文章文章标签： sql parameters sql server filter null basic

本文链接：https://blog.csdn.net/dltsxxx/article/details/2948363

版权

其他技术文章专栏收录该内容

6 篇文章 0 订阅

订阅专栏

最近又遇到分页的问题，首先就是页面程序中，然后又是SQL。网上找来找去，简单的分页有，复杂的也有；性能比较好的有，比较差的也有。呼，现在整理整理，备各种环境使用。

方法1：

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

适用于SQL SERVER 2000和2005

方法2：

 
   SELECT TOP 页大小 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum,* FROM table1
        ) A
WHERE RowNum > 页大小*(页数-1)
 
  

适用于SQL SERVER 2005

方法3：

 
   with temptbl as 
   (
    SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS RowNum, * 
    from table t  where 条件1 and 条件2
   )
SELECT * FROM temptbl
where RowNum between (页数-1)*页大小+1 and 页数*页大小
 
  

适用于SQL SERVER 2005

相关存储过程：

方法1：

 
    create procedure 存储过程名 
(@变量 Int,
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(*) from 表名 where 子句
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select 标识字段名 from 表名  where 子句 order by 排序字段名 desc
select O.*(字段列表) from 表名 O,@indextable t where O.标识字段名=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
end
set nocount off
 
   

方法2：

 
    ALTER PROCEDURE [dbo].[Pg_Paging]
@Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
@PK varchar(100),    --主键，可以带表头 a.AID
@Sort varchar(200) = '', --排序字段
@PageNumber int = 1,    --开始页码
@PageSize int = 10,        --页大小
@Fields varchar(1000) = '*',--读取字段
@Filter varchar(1000) = NULL,--Where条件
@Group varchar(1000) = NULL,  --分组
@isCount bit = 0     --1    --是否获得总记录数
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
   SET @strFilter = ' WHERE ' + @Filter + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ''
  END
if @isCount = 1 --只获得记录条数
    begin
        set @sql = 'SELECT  Count(*) FROM ' + @Tables + @strFilter  
    end
else
begin
if @Sort = ''
  set @Sort = @PK + ' DESC '
IF @PageNumber < 1
  SET @PageNumber = 1
if @PageNumber = 1 --第一页提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort
end 
else
  begin
  /**//**//**//*Execute dynamic query*/    
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
    set @sql =  ' SELECT '+@Fields+ '
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
     '+@Fields+ '
      FROM '+@Tables+') AS D
   WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
  END
END
--print @sql
EXEC(@sql)
 
   

方法3：

 
    SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   PROCEDURE Paging_Asc_Desc
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@isCount bit = 0 --1时返回记录条数
AS
/**//*Find the @PK type*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int
IF CHARINDEX('.', @PK) > 0
    BEGIN
        SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
        SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
    END
ELSE
    BEGIN
        SET @PKTable = @Tables
        SET @PKName = @PK
    END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName
IF CHARINDEX('char', @type) > 0
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strRows varchar(50)
DECLARE @strFilter varchar(8000)
DECLARE @strGroup varchar(8000)
DECLARE @strSortColumn varchar(4000)
DECLARE @strSortDesc varchar(4000)
/**//*Default Sorting*/
IF @Sort IS NULL
    SET @Sort = @PK
/**//*Set sorting variables.*/    
IF CHARINDEX('DESC',@Sort)>0
    BEGIN
        SET @strSortDesc = REPLACE(@Sort, 'DESC', 'ASC')
        SET @strSortColumn = ', ' + REPLACE(@Sort, 'DESC', '')
    END
ELSE
    BEGIN
        IF CHARINDEX('ASC', @Sort) = 0
            BEGIN
                SET @strSortDesc = @Sort + ' DESC'
                SET @strSortColumn = ', ' + @Sort
            END
        ELSE
            BEGIN
                SET @strSortDesc = REPLACE(@Sort, 'ASC', 'DESC')
                SET @strSortColumn = ', ' + REPLACE(@Sort, 'ASC', '')
            END
    END
IF @Sort = @PK
    SET @strSortColumn = ''
/**//*Default Page Number*/
IF @PageNumber < 1
    SET @PageNumber = 1
    
/**//*Set paging variables.*/
SET @strPageSize = CONVERT(varchar(50), @PageSize)
SET @strRows = CONVERT(varchar(50), (@PageSize * (@PageNumber - 1) + @PageSize))
/**//*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
    BEGIN
        SET @strFilter = ' WHERE ' + @Filter + ' '
    END
ELSE
    BEGIN
        SET @strFilter = ''
    END
IF @Group IS NOT NULL AND @Group != ''
    SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
    SET @strGroup = ''
if @isCount = 1
    begin
        EXEC('SELECT  Count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup )
    end
else
    begin
    /**//*Execute dynamic query*/    
    EXEC(
    'DECLARE @tblPK TABLE (
                PK  ' + @type + ' NOT NULL PRIMARY KEY
                )
    
    INSERT INTO @tblPK SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM (SELECT TOP ' + @strRows + ' ' + @PK + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ') AS ' + @PKTable + ' ORDER BY ' + @strSortDesc + '
    
    SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
    )
    end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
   

方法4：分2000和2005

 
     Create PROCEDURE [dbo].[Basic_Pagination2000]
    @tblName      varchar(255),   -- 表名
    @fidlelist    varchar(2000),  --要查询字段
    @fldName      varchar(255),   -- 排序字段
    @PageSize     int,            -- 页尺寸
    @PageIndex    int,            -- 页码
    @IsReCount    bit,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000)   -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(100),@tmpwhere  varchar(200)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
if @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by [' + @fldName +'] asc'
end
set @tmpwhere='';
if(@strWhere!='')
begin
   set @tmpwhere=' where '+@strWhere;
end
if @PageIndex = 1
begin
    set @strSQL = 'select top ' + str(@PageSize) +' '+@fidlelist+' '+'from ['
        + @tblName + '] ' + @tmpwhere + ' ' + @strOrder
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' '+@fidlelist+' '+'from ['
    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
    + @fldName + '] from [' + @tblName + '] ' + @tmpwhere + ' '
    + @strOrder + ') as tblTmp)  ' + @tmpwhere + ' ' + @strOrder
end
exec(@strSQL)
if @IsReCount != 0
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+ @strWhere
    exec (@strSQL)
   end
ALTER PROCEDURE [dbo].[Basic_Pagination2005]
  @tblName      nvarchar(200),     --表名
  @fidlelist    nvarchar(1000),   --要查询字段
  @fldName      nvarchar(100),    --排序字段
  @PageSize     int,              --页尺寸
  @PageIndex    int,              --页码
  @IsReCount    bit ,             -- 返回记录总数, 非 0 值则返回
  @OrderType    bit,              -- 设置排序类型, 非 0 值则降序
  @strWhere nvarchar(1000)        --查询条件
AS
  declare @sqlstr nvarchar(4000),@tmpwhere nvarchar(4000),@tmporder nvarchar(100)
BEGIN
    if @OrderType != 0
    begin
        set @tmporder = @fldName +' desc '
    end
    else
    begin
        set @tmporder = @fldName +' asc '
    end
    set @tmpwhere='';
    if(@strWhere!='')
    begin
       set @tmpwhere=' where '+@strWhere;
    end
    set @sqlstr=N'select * from(select  '+@fidlelist+', ROW_NUMBER() OVER(order by '+@tmporder+') as row from '+@tblName+@tmpwhere+') tmp where row between '+cast(((@PageIndex-1)*@PageSize+1) as nvarchar)+' and '+cast(@PageIndex*@PageSize as nvarchar);  
    exec sp_executesql @sqlstr
    if @IsReCount != 0
    begin
      set @sqlstr=N'select count(*) as Total from '+ @tblName+@tmpwhere
      exec sp_executesql @sqlstr    
    end
END
 
    

方法5：这个比较有创意

 
      使用系统表根据存储过程名字生成ADO.NET数据库访问代码 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[sqltoolforexcuteandadapter]
(
@objName nvarchar(100),--存储过程名称
@isexcute int --是否为execute 或者是sqladapter  0是execute,1是sqladapter
)
AS
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)
--Change the following variable to the name of your connection instance
SET @connName='conn.Connection'
SET @parameterAt=''
SET @outputValues=''
SELECT
        dbo.sysobjects.name AS ObjName,
        dbo.sysobjects.xtype AS ObjType,
        dbo.syscolumns.name AS ColName,
        dbo.syscolumns.colorder AS ColOrder,
        dbo.syscolumns.length AS ColLen,
        dbo.syscolumns.colstat AS ColKey,
        dbo.syscolumns.isoutparam AS ColIsOut,
        dbo.systypes.xtype
INTO #t_obj
FROM
        dbo.syscolumns INNER JOIN
        dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
        dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE
        (dbo.sysobjects.name = @objName)
        AND
        (dbo.systypes.status <> 1) 
ORDER BY
        dbo.sysobjects.name,
        dbo.syscolumns.colorder
SET @parameterCount=(SELECT count(*) FROM #t_obj)
IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
IF(@errMsg is null)
        BEGIN
                print 'SqlConnection conn = new SqlConnection("");
            SqlCommand com = new SqlCommand("'+@objName+'", conn);'
                print 'com.CommandType = CommandType.StoredProcedure;'
                PRINT '   SqlParameter[] Parameters = new SqlParameter[' +
cast(@parameterCount as varchar) + '];'
                PRINT ''
                DECLARE @source_name nvarchar,
                                @source_type varchar,
                        @col_name nvarchar(100),
                                @col_order int,
                                @col_type varchar(20),
                        @col_len int,
                                @col_key int,
                                @col_xtype int,
                                @col_redef varchar(20),
                                @col_isout tinyint
                DECLARE cur CURSOR FOR
                SELECT * FROM #t_obj
                OPEN cur
                -- Perform the first fetch.
                FETCH NEXT FROM cur INTO
@source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
                        if(@source_type=N'U') SET @parameterAt='@'
                        -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
                        WHILE @@FETCH_STATUS = 0
                                BEGIN
                                SET @col_redef=(SELECT CASE @col_xtype
                                        WHEN 34 THEN 'Image'
                                        WHEN 35 THEN 'Text'
                                        WHEN 36 THEN 'UniqueIdentifier'
                                        WHEN 48 THEN 'TinyInt'
                                        WHEN 52 THEN 'SmallInt'
                                        WHEN 56 THEN 'Int'
                                        WHEN 58 THEN 'SmallDateTime'
                                        WHEN 59 THEN 'Real'
                                        WHEN 60 THEN 'Money'
                                        WHEN 61 THEN 'DateTime'
                                        WHEN 62 THEN 'Float'
                                        WHEN 99 THEN 'NText'
                                        WHEN 104 THEN 'Bit'
                                        WHEN 106 THEN 'Decimal'
                                        WHEN 122 THEN 'SmallMoney'
                                        WHEN 127 THEN 'BigInt'
                                        WHEN 165 THEN 'VarBinary'
                                        WHEN 167 THEN 'VarChar'
                                        WHEN 173 THEN 'Binary'
                                        WHEN 175 THEN 'Char'
                                        WHEN 231 THEN 'NVarChar'
                                        WHEN 239 THEN 'NChar'
                                        ELSE '!MISSING'
                                        END AS C)
                                --Write out the parameter
                                PRINT '   Parameters[' + cast(@col_order-1 as varchar)
                                    + '] = new SqlParameter("' + @parameterAt + @col_name
                                    + '", SqlDbType.' + @col_redef
                                    + ');'
                                --Write out the parameter direction it is output
                                IF(@col_isout=1)
                                        BEGIN
                                                PRINT '   Parameters['+ cast(@col_order-1 as varchar)
+'].Direction=ParameterDirection.Output;'
                                                SET @outputValues=@outputValues+'   ?=Parameters['+
cast(@col_order-1 as varchar) +'].Value;'
                                        END
                                        ELSE
                                        BEGIN
                                                --Write out the parameter value line
                                                PRINT '   Parameters['+ cast(@col_order-1 as varchar) + '].Value = ?;'
                                        END
                                --If the type is a string then output the size declaration
                                IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
                                        BEGIN
                                                PRINT '   Parameters[' + cast(@col_order-1 as varchar) +
'].Size=' + cast(@col_len as varchar) + ';'
                                        END
                                 -- This is executed as long as the previous fetch succeeds.
                        FETCH NEXT FROM cur INTO
@source_name,@source_type,@col_name,@col_order,
@col_len,@col_key,@col_isout,@col_xtype
        END
  PRINT ''
  print '       com.Parameters.AddRange(Parameters);'
if      @isexcute = 0 --使用的execute方法执行sql语句
begin
      print 'try
            {
                conn.Open();
                com.ExecuteNonQuery();
            }
            catch (Exception ee)
            {
                throw ee;
            }
            finally
            {
                conn.Close();
            }'
end
else if @isexcute = 1--需要返回数据集的话使用这个
begin
        print 'try
            {
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                throw ee;
            }
            finally
            {
               //do what you want to do or dispose resoures. 
            }'
end
  CLOSE cur
  DEALLOCATE cur
 END
if(LEN(@errMsg)>0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
 
     

 
       测试代码： sqltoolforexcuteandadapter 'YourProcName',1--or 0
显示出来的结果是：
SqlConnection conn = new SqlConnection("");
            SqlCommand com = new SqlCommand("YourProcName", conn);
com.CommandType = CommandType.StoredProcedure;
   SqlParameter[] Parameters = new SqlParameter[1];
   Parameters[0] = new SqlParameter("@yourparam", SqlDbType.VarChar);
   Parameters[0].Value = ?;
   Parameters[0].Size=6;
       com.Parameters.AddRange(Parameters);
try
            {
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                throw ee;
            }
            finally
            {
               //do what you want to do or dispose resoures. 
            }
 
      

恩，方法很多，虽然有些都大同小异，争论点也很多，比如要不要拼接SQL语句。有人说损失性能比较大，但又没给出具体解决方案。还有就是有的遇到100W条记录的时候，分页只给显示到多少记录位置，恩，这个是很好提议。100W记录的话，确实没人会一条条的查看完整的。

呼。问题还多。。。还得自己整理。毕竟东西是别人的，拿来主意不太好。

LightColors

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL 分页，又见分页。

最近又遇到分页的问题，首先就是页面程序中，然后又是SQL。网上找来找去，简单的分页有，复杂的也有；性能比较好的有，比较差的也有。呼，现在整理整理，备各种环境使用。方法1：SELECT TOP 页大小 *FROM table1WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM tab
复制链接

扫一扫

专栏目录