有一表(ProductInfo)有20万条记录，写出它的分页sql语句。表中有ID(int) 标识列，要求性能好，效率高！注：区限记录(beginrecord,endrecord) 每页20记录 当前页pageIndex
我写了两个：
1、select * from (select *,ROW_NUMBER() OVER(ORDER BY ID Asc) AS 'rownumber' from ProductInfo) T
where T.rownumber between beginrecord and endrecord
2、select top(20) * from ProductInfo
where (id not in (select top(20*(pageIndex-1)) ID from ProductInfo order by ID))
order by ID Asc
面试官说这两个行还是可以，但效率低，耗性能，听他意思还有其他方法，望高手指点！
补充一个问题：
SQL SERVER中的表存到多少条记录时，速度明显变慢？
select top 5 * from user where uid not in (select top 5 *(2 -1)uid from user) (第二页，每页5条)
select top [pagesize] * from [table] where [uid] not in (select top [pagesize] *([pageno] -1)[uid from] [table])
用他们表中的ID来查！
我有个才存储过程的写法
--************************************************** --ProductT --Create Procedure [dbo].[Pro_GetLanTabPage] -- ALTER Proc [dbo].[Pro_GetLanTabPage] ( @SqlWhere varchar(1000)=null,--添加参数，【语言】 @ParentTableName VARCHAr(50)=NULL,--添加参数，【表名】 @PrimaryField varchar(50),--主键字段名 @sortExpression varchar(100),--排序条件 @PageIndex int, @PageSize int, @TotalNum int output, @TotalPage int output ) as begin Declare @sqlCount nvarchar(4000) Declare @sqlstr nvarchar(4000) Declare @GetSql as nvarchar(4000) set @sqlCount=N'select @TotalNum=count(*) ' set @sqlstr=N'select ROW_NUMBER() over(order by '+@sortExpression+') as rowId,tb.*,a.LanguageType,a.SiteName ' set @GetSql=N' from '+@ParentTableName+' as tb inner join AdvertisementNewsParaT as a on a.ParentID=tb.'+@PrimaryField +' where ParentTableName='''+@ParentTableName+'''' if(@SqlWhere is not null) set @GetSql=@GetSql+@SqlWhere print @GetSql set @sqlCount=@sqlCount+@GetSql EXEC sp_executesql @sqlCount,N'@TotalNum int OUTPUT',@TotalNum OUTPUT
Declare @StartRecord int Declare @EndRecord int select @TotalPage=CEILING((@TotalNum+0.0)/@PageSize) if @PageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage Set @pageIndex = @TotalPage set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1
set @sqlstr='select * from ('+@sqlstr+@GetSql+') as tb where rowId between '+convert(varchar(9),@StartRecord)+' and '+convert(varchar(9),@EndRecord) EXEC (@sqlStr)
end
给以关注，我也是经常那样写的，有最佳答案了告诉我！