记录一个古老的Sql分页过程
1 /* 2 根据单位ID获取排班信息 3 For:WXX 4 TIme:2017-11-22 5 */ 6 ALTER proc [dbo].[proc_ScheduleInfo] 7 @companyID nvarchar(20)='', --单位ID 8 @classLineId nvarchar(20)='', --线路ID 9 @vehicleId nvarchar(20)='', --车辆ID 10 @goTime nvarchar(30) ='', --发车日期 11 @pageIndex int=1, --当前页 12 @pageSize int=1000, --页容量 13 @numsCount int=0 output, --总条数 14 @pageCount int=0 output --总页数 15 16 As 17 18 --declare @dateNow nvarchar(20) --当前日期 19 --DECLARE @sql varchar(max) --查询sql 20 --DECLARE @sqlCount varchar(max) --查询countsql 21 --DECLARE @top int 22 declare @countSql nvarchar(max) --总条数SQL 23 declare @infoSql nvarchar(max) --数据SQL 24 declare @pageSql nvarchar(max) --分页SQL 25 declare @whereSql nvarchar(max) --条件SQL 26 declare @execSql nvarchar(max) --总条数执行SQL 27 declare @couns nvarchar(max) 28 29 set @whereSql = ' 1=1 ' 30 31 if(@companyID is not null and @companyID <>'') 32 set @whereSql = @whereSql+' and companyID= '''+@companyID+''' ' 33 if(@classLineId is not null and @classLineId <>'') 34 set @whereSql = @whereSql+' and classLineId = '+@classLineId+' ' 35 if(@vehicleId is not null and @vehicleId <>'') 36 set @whereSql = @whereSql+' and vehicleId = '+@vehicleId+' ' 37 if(@goTime is not null and @goTime <>'') 38 set @whereSql = @whereSql+' and date like '''+@goTime+'%'' ' 39 40 --查询总条数sql 41 set @countSql = 'select @count=COUNT(1) from ( 42 43 select s.*, d.remark as dr from 44 ( 45 select * from Schedule where '+@whereSql+' 46 ) as s 47 join Company as d on s.companyID = d.cNo 48 ) as b ' 49 50 --查询数据sql 51 set @infoSql = 'SELECT Top ('+CONVERT(nvarchar(10),@pageSize)+') * from ( 52 select row_number()over(order by date)rownumber,* from 53 ( 54 select s.*, d.remark as dr from 55 ( 56 select * from Schedule where '+@whereSql+' 57 ) as s 58 join Company as d on s.companyID = d.cNo 59 ) as b ' 60 61 set @pageSql = ' ) as T 62 Where rownumber >= ('+CONVERT(nvarchar(10),@pageIndex)+'-1) * ('+CONVERT(nvarchar(10),@pageSize)+')+1 Order By rownumber' 63 64 65 66 --总页数 67 68 set @execSql = @countSql 69 --select(@execSql) for xml path('') 70 exec sp_executesql @execSql, N'@count int out', @couns out 71 set @numsCount = @couns 72 73 --分页查询 74 --select(@infoSql+@whereSql+@pageSql) for xml path('') 75 exec(@infoSql+@pageSql) 76 77 78 --分页算法 79 SET @pageCount = @numsCount % @pageSize; 80 if (@pageCount =0) 81 begin 82 set @pageCount = @numsCount / @pageSize ; 83 end 84 else if(@numsCount<@pageSize) 85 begin 86 set @pageCount=1; 87 end 88 else 89 begin 90 set @pageCount = @numsCount / @pageSize + 1; 91 end 92 if (@pageIndex>@PageCount) 93 begin 94 set @pageIndex = @pageCount; 95 end
好记性不如云存储。