SQL SERVER实现分页查询

[董鲜森]

java.lang.RuntimeException: java.sql.SQLException: 'PAGESIZE' 附近有语法错误。 Query: select top PAGESIZE * from t_book where 1=1 and cid = ? and orderBy not in (select top PAGESIZE*(PAGENUM-1) orderBy from t_book order by orderBy) order by orderBy

求大神解答？？？

[AcHerat]

top PAGESIZE 这里的PAGESIZE用变量传进去，后边的也是，在java中计算好，直接传入变量 btw: 建议采用SQL的分页存储过程，不要直接在程序中拼接分页代码！

[Cherise_huang]

分享一个分页存储过程例子：

create PROCEDURE [dbo].[usp_Get_Record_ByPage]
@tblName   varchar(255),       -- 表名
@fldName varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='',      -- 排序的字段名，必填。后面跟着排序方式，如UserName Desc ,多个排序字段用逗号分开，
@PageSize   int = 10,          -- 页尺寸,默认10
@PageIndex int = 1,           -- 页码,默认1
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@TotalCount int output              --返回记录总数
AS

declare @strSQL   varchar(5000)

if @strWhere !=''
set @strWhere=' where '+@strWhere
if @TotalCount is null
begin
declare @totalSql nvarchar(4000)
 set @totalSql='SELECT @TotalCount=COUNT(*) FROM '+@tblName+' '+@strWhere
 exec sp_executesql @totalSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
end
set @strSQL=
'SELECT * FROM ('+
	'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+') AS pos,'+@fldName+' '+
	'FROM '+@tblName+' '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)

  
exec (@strSQL)
GO

[xiaoxiangqing]

最后运行要么是一句sql，如果要带参数的话，就用sp_executesql

[Tiger_Zhao]

java代码里这样拼SQL

sql = "select top "+PAGESIZE+" * from t_book"+
      " where 1=1 and cid = ? and orderBy not in"+
      " (select top "+(PAGESIZE*(PAGENUM-1))+" orderBy from t_book order by orderBy)"+
      " order by orderBy";

[LongRui888]

尽量用row_number来实现分页吧，效率比较高。

declare @page_size int;
declare @page_num int;

--比如：每页10条记录
set @page_size = 10;

--比如：先取第1页
set @page_num = 1;

select *
from
(
select *,   
       row_number() over(order by @@servername) as rownum,
       --这里按照@@servername来排序，
       --你可以根据需要按照id，sid，sname等字段来排序
       (row_number() over(order by @@servername) - 1) / @page_size as pagenum
from sys.objects
)t
where pagenum = @page_num - 1