createPROCEDURE ProcCustomPageForTaxisField ( @Table_Namevarchar(1000), /**//* 查询的表名 */ @Sign_Recordvarchar(50), /**//* 标志字段 */ @Filter_Conditionvarchar(1000), /**//* 过滤条件 */ @Page_Sizeint, /**//* 每页记录数 */ @Page_Indexint, /**//* 页号 */ @TaxisFieldvarchar(50), /**//* 排序字段 */ @Find_RecordListvarchar(1000), /**//* 查询的字段,字段间用,分割 空为查询全部*/ @Record_Countint/**//* 总记录数 */ ) AS BEGIN DECLARE@Page_Countint DECLARE@Start_Numberint DECLARE@End_Numberint DECLARE@TopN_Numberint DECLARE@sSQLvarchar(5000) DECLARE@sSQL2varchar(5000) if(@Find_RecordList='') BEGIN SELECT@Find_RecordList=@Table_Name+'.*' END if(@Filter_Condition='') BEGIN SELECT@Filter_Condition='1=1' END if(@Page_Index=1) BEGIN SELECT@sSQL='SELECT top '+CAST(@Page_SizeASVARCHAR(10))+''+@Find_RecordList+' from '+@Table_Name+' where '+@Filter_Condition+' order by '+@TaxisField EXEC (@sSQL) END else BEGIN if((@Record_Count%@Page_Size)!<1) SELECT@Page_Count=@Record_Count/@Page_Size+1 else SELECT@Page_Count=@Record_Count/@Page_Size if(@Page_Index>@Page_Count) SELECT@Page_Index=@Page_Count SELECT@Start_Number=(@Page_Index-1) *@Page_Size+1 IF@Start_Number<=0 SElECT@Start_Number=0 SELECT@End_Number=@Start_Number+@Page_Size-1 IF@End_Number>@Record_Count SELECT@End_Number=@Record_Count createtable #firsttableAAA (orderidAAA INTPRIMARYKEYIDENTITY(1,1),tableid2AAA int) select@sSQL='insert into #firsttableAAA(tableid2AAA) select top '+CAST(@End_NumberASVARCHAR(10))+''+@Sign_Record+' from '+@Table_Name+' t1 where '+@Filter_Condition+' order by '+@TaxisField EXEC(@sSQL) select@sSQL2='select '+@Table_Name+'.'+@Find_RecordList+' from #firsttableAAA inner join '+@Table_Name+' on #firsttableAAA.tableid2AAA='+@Sign_Record+' where orderidAAA>(select max(orderidAAA) from (select top '+CAST(@Start_Number-1ASVARCHAR(10))+' orderidAAA from #firsttableAAA) as t) order by orderidAAA' EXEC(@sSQL2) droptable #firsttableAAA END end
简单来说就是
create table #firsttable (orderid INT PRIMARY KEY IDENTITY(1,1),tableid2 int) 临时表 insert into #firsttable(tableid2) select top 20 id from dbo.TABLE1 t1 order by t1.aa desc, t1.id 比如查21-30的数据,那查出前30个记录插入到临时表中 select tableid2,dbo.TABLE1.* from #firsttable inner join dbo.TABLE1 on #firsttable.tableid2=dbo.TABLE1.id where orderid>(select max(orderid) from (select top 10 orderid from #firsttable) as t) order by orderid drop table #firsttable 临时表中把id>第20条的数据取出来,剩下的就是要的数据,用inner join 联合查出.
后来我又发现一个更高效的
先说说基本原理,比如3页,每页10条,就是求21-30条数据,总共30条记录我们是知道的,用 set Rowcount 30设置只查前30条数据,用@n=10(pagesize) select @n=@n-1,Case @n<10 then @sql=sql+id+',' from 从而得到21,22,23,24....30字符串,@n<10代表从上往下数,最后10个
createPROC PageView @tbname sysname, --要分页显示的表名 @FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrentint=1, --要显示的页码 @PageSizeint=10, --每页的大小(记录数) @FieldShownvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrdernvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC --用于指定排序顺序 @Wherenvarchar(1000)='', --查询条件 @PageCountint OUTPUT --总页数 AS DECLARE@sqlnvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IFOBJECT_ID(@tbname) ISNULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@tbname) RETURN END IFOBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 ANDOBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 ANDOBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) RETURN END --分页字段检查 IFISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END --其他参数检查及规范 IFISNULL(@PageCurrent,0)<1SET@PageCurrent=1 IFISNULL(@PageSize,0)<1SET@PageSize=10 IFISNULL(@FieldShow,N'')=N''SET@FieldShow=N'*' IFISNULL(@FieldOrder,N'')=N'' SET@FieldOrder=N'' ELSE SET@FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IFISNULL(@Where,N'')=N'' SET@Where=N'' ELSE SET@Where=N'WHERE ('+@Where+N')' --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF@PageCountISNULL BEGIN SET@sql=N'SELECT @PageCount=COUNT(*)' +N' FROM '+@tbname +N''+@Where EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT SET@PageCount=(@PageCount+@PageSize-1)/@PageSize END --计算分页显示的TOPN值 DECLARE@TopNvarchar(20),@TopN1varchar(20) SELECT@TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 IF@PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N''+@FieldShow +N' FROM '+@tbname +N''+@Where +N''+@FieldOrder) ELSE BEGIN SELECT@PageCurrent=@TopN1, @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname +N''+@Where +N''+@FieldOrder SETROWCOUNT@PageCurrent EXEC sp_executesql @sql, N'@n int,@s nvarchar(4000) OUTPUT', @PageCurrent,@sql OUTPUT SETROWCOUNT0 IF@sql=N'' EXEC(N'SELECT TOP 0' +N''+@FieldShow +N' FROM '+@tbname) ELSE BEGIN SET@sql=STUFF(@sql,1,1,N'') --执行查询 EXEC(N'SELECT TOP '+@TopN +N''+@FieldShow +N' FROM '+@tbname +N' WHERE '+@FieldKey +N' IN('+@sql +N') '+@FieldOrder) END END