我的T-SQL分页备忘

两次top+过半两分:

1,step1

DECLARE @page_size int --页大小 DECLARE @page_index int --当前页码 --------------------------- --示例数据 SELECT @page_size=10 SELECT @page_index=8 --------------------------- DECLARE @record_count int --总记录数 SELECT @record_count = count(1) FROM Productsmall DECLARE @page_count int --总页数 SELECT @page_count = @record_count/@page_size DECLARE @top1_asc VARCHAR(8) --第一层top,最里层top,二分法的前一半记录 SELECT @top1_asc = cast((@page_size * @page_index) AS varchar(8)) DECLARE @top1_desc VARCHAR(8) --第一层top,最里层top,二分法的后一半记录,与前一半的记录条数并不一样,要视余数多少 --SELECT @top1_desc = cast((@record_count - (@page_index-1) * @page_size ) AS varchar(8)) DECLARE @top1_desc_i INT SELECT @top1_desc_i= @record_count - (@page_index-1) * @page_size if(@top1_desc_i<0) select @top1_desc_i=0 SELECT @top1_desc = cast(@top1_desc_i AS varchar(8)) DECLARE @top2 VARCHAR(8) --第二层top,用于取page_size个记录 SELECT @top2 = cast((@page_size) AS varchar(8)) declare @sql varchar(3600) select @sql = '' select @sql = @sql + 'SELECT * ' select @sql = @sql + 'FROM ( ' select @sql = @sql + ' SELECT TOP '+ @top2 +' * '--@page_size select @sql = @sql + ' FROM ' select @sql = @sql + ' ( ' IF(@page_index<=@page_count/2) select @sql = @sql + ' SELECT TOP '+ @top1_asc +' Pkid,ProductName,CategoryPkid ' --二分法:前一半 ELSE select @sql = @sql + ' SELECT TOP '+ @top1_desc +' Pkid,ProductName,CategoryPkid ' --二分法:后一半 select @sql = @sql + ' FROM Productsmall ' select @sql = @sql + ' WHERE 1=1 ' IF(@page_index<=@page_count/2) select @sql = @sql + ' ORDER BY Pkid asc ' --二分法:前一半 ELSE select @sql = @sql + ' ORDER BY Pkid desc ' --二分法:后一半 select @sql = @sql + ' ) t1 ' IF(@page_index<=@page_count/2) select @sql = @sql + ' ORDER BY Pkid desc ' --二分法:前一半 ELSE select @sql = @sql + ' ORDER BY Pkid asc ' --二分法:后一半 select @sql = @sql + ') t2 ' select @sql = @sql + 'ORDER BY Pkid asc ' --CategoryPkid ASC --Pkid asc -- 实际业务逻辑中需要的排序 PRINT @sql exec(@sql) go

 

2,step2

DECLARE @page_size int --页大小 DECLARE @page_index int --当前页码 DECLARE @tables VARCHAR(512) --表,sql中from后where前的一大串 DECLARE @fields VARCHAR(1024) --字段 DECLARE @masterUniquePkid VARCHAR(32) --用于top分页排序的依据的唯一PKID --------------------------- --示例数据 SELECT @page_size=10 SELECT @page_index=8 SELECT @tables=' Productsmall p INNER join Categories c ON p.CategoryPkid=c.Pkid ' SELECT @fields=' p.* ' SELECT @masterUniquePkid='p.Pkid' --------------------------- DECLARE @record_count int --总记录数 --SELECT @record_count = count(1) FROM Productsmall DECLARE @sql_record_count NVARCHAR(1000) SELECT @sql_record_count = N'SELECT @record_count = count(1) FROM ' + @tables EXEC sp_executesql @sql_record_count,N'@record_count int output',@record_count OUTPUT --PRINT @record_count DECLARE @page_count int --总页数 SELECT @page_count = @record_count/@page_size DECLARE @top1_asc VARCHAR(8) --第一层top,最里层top,二分法的前一半记录 SELECT @top1_asc = cast((@page_size * @page_index) AS varchar(8)) DECLARE @top1_desc VARCHAR(8) --第一层top,最里层top,二分法的后一半记录,与前一半的记录条数并不一样,要视余数多少 --SELECT @top1_desc = cast((@record_count - (@page_index-1) * @page_size ) AS varchar(8)) DECLARE @top1_desc_i INT SELECT @top1_desc_i= @record_count - (@page_index-1) * @page_size if(@top1_desc_i<0) select @top1_desc_i=0 SELECT @top1_desc = cast(@top1_desc_i AS varchar(8)) DECLARE @top2 VARCHAR(8) --第二层top,用于取page_size个记录 SELECT @top2 = cast((@page_size) AS varchar(8)) declare @sql varchar(3600) select @sql = '' select @sql = @sql + 'SELECT * ' select @sql = @sql + 'FROM ( ' select @sql = @sql + ' SELECT TOP '+ @top2 +' * ,t2Pkid = t1Pkid '--@page_size select @sql = @sql + ' FROM ' select @sql = @sql + ' ( ' IF(@page_index<=@page_count/2) select @sql = @sql + ' SELECT TOP '+ @top1_asc + @fields + ',t1Pkid=' + @masterUniquePkid --二分法:前一半 ELSE select @sql = @sql + ' SELECT TOP '+ @top1_desc + @fields + ',t1Pkid=' + @masterUniquePkid --二分法:后一半 select @sql = @sql + ' FROM ' + @tables + ' ' select @sql = @sql + ' WHERE 1=1 ' IF(@page_index<=@page_count/2) select @sql = @sql + ' ORDER BY '+ @masterUniquePkid +' asc ' --二分法:前一半 ELSE select @sql = @sql + ' ORDER BY '+ @masterUniquePkid +' desc ' --二分法:后一半 select @sql = @sql + ' ) t1 ' IF(@page_index<=@page_count/2) select @sql = @sql + ' ORDER BY t1.t1Pkid desc ' --二分法:前一半 ELSE select @sql = @sql + ' ORDER BY t1.t1Pkid asc ' --二分法:后一半 select @sql = @sql + ') t2 ' select @sql = @sql + 'ORDER BY t2.t2Pkid asc ' --CategoryPkid ASC --Pkid asc -- 实际业务逻辑中需要的排序 PRINT @sql exec(@sql) go

 

3,step3,实际业务逻辑分页

select * --3. from ( SELECT TOP 10 * --2 FROM ( SELECT TOP 30 Pkid,ProductName,CategoryPkid,QuantityPerUnit,UnitPrice --1. FROM Productsmall WHERE 1=1 ORDER BY UnitPrice desc , Pkid desc --1.实际业务逻辑 ) t1 ORDER BY UnitPrice asc , Pkid asc --2.倒过来 ) t2 order by UnitPrice desc , Pkid desc --3.实际业务逻辑

 

DECLARE @page_size int --页大小 DECLARE @page_index int --当前页码 DECLARE @tables VARCHAR(512) --表,sql中from后where前的一大串 DECLARE @fields VARCHAR(1024) --字段 DECLARE @masterUniquePkid VARCHAR(32) --用于top分页排序的依据的唯一PKID DECLARE @where VARCHAR(1024) --条件,如" UnitPrice>10.00 and CategoryPkid=2 " DECLARE @order_logic VARCHAR(256) --排序,如" CategoryPkid asc, UnitPrice desc " DECLARE @order_desc VARCHAR(256) --排序,如" CategoryPkid desc,UnitPrice asc " --与@order_asc相反,由于t-sql字符串处理能力有限... ----------------------------------------------------------------------------------- --示例数据 SELECT @page_size=10 SELECT @page_index=1 SELECT @tables=' Productsmall p INNER join Categories c ON p.CategoryPkid=c.Pkid ' SELECT @fields=' p.* ' SELECT @masterUniquePkid='p.Pkid' SELECT @where= null --' p.UnitPrice>10.00 and p.CategoryPkid=2 ' SELECT @order_logic = ' UnitPrice desc ' --todo:有问题 SELECT @order_desc = ' UnitPrice asc ' --todo:有问题 ----------------------------------------------------------------------------------- --参数检验开始-- --@tables前后加空格 --@fields前后加空格 --@masterUniquePkid前后加空格 --@where前后加空格 --@order多条件与逗号分隔,最后再加空格,因为后面还有一个按主键排序(为了防止业务逻辑排序重复的情况下也能有一个顺序) if((@order_logic is not null) and (@order_desc is not null)) begin set @order_logic = rtrim(@order_logic) if(len(@order_logic)>=5) --'最短:f asc' begin if(right(@order_logic,1)<>',') set @order_logic =@order_logic + ',' end set @order_desc = rtrim(@order_desc) if(len(@order_desc)>=5) --'最短:f desc' begin if(right(@order_desc,1)<>',') set @order_desc =@order_desc+',' end else set @order_desc = '' end else begin set @order_logic = ' ' set @order_desc = ' ' end --参数检验结束-- DECLARE @record_count int --总记录数 --SELECT @record_count = count(1) FROM Productsmall DECLARE @sql_record_count NVARCHAR(1000) SELECT @sql_record_count = N'SELECT @record_count = count(1) FROM ' + @tables + ' where 1=1 ' if(@where IS not NULL) if(len(@where)>=3) SELECT @sql_record_count = @sql_record_count + ' and ' + @where + ' ' EXEC sp_executesql @sql_record_count,N'@record_count int output',@record_count OUTPUT --PRINT @record_count DECLARE @page_count int --总页数 SELECT @page_count = @record_count/@page_size DECLARE @top1_asc VARCHAR(8) --第一层top,最里层top,二分法的前一半记录 SELECT @top1_asc = cast((@page_size * @page_index) AS varchar(8)) DECLARE @top1_desc VARCHAR(8) --第一层top,最里层top,二分法的后一半记录,与前一半的记录条数并不一样,要视余数多少 --SELECT @top1_desc = cast((@record_count - (@page_index-1) * @page_size ) AS varchar(8)) DECLARE @top1_desc_i INT SELECT @top1_desc_i= @record_count - (@page_index-1) * @page_size if(@top1_desc_i<0) select @top1_desc_i=0 SELECT @top1_desc = cast(@top1_desc_i AS varchar(8)) DECLARE @top2 VARCHAR(8) --第二层top,用于取page_size个记录 SELECT @top2 = cast((@page_size) AS varchar(8)) declare @sql varchar(3600) select @sql = '' select @sql = @sql + 'SELECT * ' --No3.排序. select @sql = @sql + 'FROM ( ' select @sql = @sql + ' SELECT TOP '+ @top2 +' * ,t2Pkid = t1Pkid '--@page_size --No2.排序. select @sql = @sql + ' FROM ' select @sql = @sql + ' ( ' IF(@page_index<=@page_count/2) select @sql = @sql + ' SELECT TOP '+ @top1_asc + @fields + ',t1Pkid=' + @masterUniquePkid --二分法:前一半 ELSE select @sql = @sql + ' SELECT TOP '+ @top1_desc + @fields + ',t1Pkid=' + @masterUniquePkid --二分法:后一半 select @sql = @sql + ' FROM ' + @tables + ' ' select @sql = @sql + ' WHERE 1=1 ' if(@where IS not NULL) if(len(@where)>=3) select @sql = @sql + ' and ' + @where IF(@page_index<=@page_count/2) select @sql = @sql + ' ORDER BY '+ @order_logic + @masterUniquePkid +' asc ' --二分法:前一半 --No1.排序.实际业务逻辑todo ELSE select @sql = @sql + ' ORDER BY '+ @order_desc + @masterUniquePkid +' desc ' --二分法:后一半 --No1.排序.实际业务逻辑todo select @sql = @sql + ' ) t1 ' IF(@page_index<=@page_count/2) select @sql = @sql + ' ORDER BY '+ @order_desc +' t1.t1Pkid desc ' --二分法:前一半 --No2.排序.倒过来 ELSE select @sql = @sql + ' ORDER BY '+ @order_logic +' t1.t1Pkid asc ' --二分法:后一半 --No2.排序.倒过来 select @sql = @sql + ') t2 ' select @sql = @sql + 'ORDER BY ' + @order_logic + ' t2.t2Pkid asc ' --No3.排序.实际业务逻辑 PRINT @sql exec(@sql) GO