059 --------生成查询语句-------- 060 --此处@strTmp为取得查询结果数量的语句 061 if @strCondition is null or @strCondition='' --没有设置显示条件 062 begin 063 set @sqlTmp = @fldName + ' From ' + @tblName 064 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName 065 set @strID = ' From ' + @tblName 066 end 067 else 068 begin 069 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition 070 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition 071 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition 072 end 073 074 ----取得查询结果总数量----- 075 exec sp_executesql @strTmp,N'@Counts int out ',@Counts out 076 declare @tmpCounts int 077 if @Counts = 0 078 set @tmpCounts = 1 079 else 080 set @tmpCounts = @Counts 081 082 --取得分页总数 083 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 084 085 /**//**//**//**当前页大于总页数 取最后一页**/ 086 if @page>@pageCount 087 set @page=@pageCount 088 089 --/*-----数据分页2分处理-------*/ 090 declare @pageIndex int --总数/页大小 091 declare @lastcount int --总数%页大小 092 093 set @pageIndex = @tmpCounts/@pageSize 094 set @lastcount = @tmpCounts%@pageSize 095 if @lastcount > 0 096 set @pageIndex = @pageIndex + 1 097 else 098 set @lastcount = @pagesize 099 100 --//***显示分页 101 if @strCondition is null or @strCondition='' --没有设置显示条件 102 begin 103 if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 104 begin 105 if @page=1 106 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 107 +' order by '+ @fldSort +' '+ @strFSortType 108 else 109 begin 110 if @Sort=1 111 begin 112 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 113 +' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName 114 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 115 +' order by '+ @fldSort +' '+ @strFSortType 116 end 117 else 118 begin 119 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 120 +' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName 121 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 122 +' order by '+ @fldSort +' '+ @strFSortType 123 end 124 end 125 end 126 else 127 begin 128 set @page = @pageIndex-@page+1 --后半部分数据处理 129 if @page <= 1 --最后一页数据显示 130 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName 131 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 132 else 133 if @Sort=1 134 begin 135 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 136 +' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName 137 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 138 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 139 end 140 else 141 begin 142 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 143 +' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName 144 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 145 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 146 end 147 end 148 end |