我仍然试图用PHP创建分页,但SQL SERVER(2008)不支持偏移量和限制。下面你可以看到SQL查询,但我不知道出了什么问题:
declare @offset int
declare @limit int
set @offset = 2;
set @limit = 20;
SELECT *, ROW_NUMBER() OVER (ORDER BY use_by_date desc ) AS RowNum FROM DB
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit
只需将您的查询嵌套在另一个查询中:
SELECT t.*
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY use_by_date desc ) AS RowNum
FROM DB) t
WHERE t.RowNum >= @Offset AND t.RowNum < @Offset + @Limit
窗口函数(如 ROW_NUMBER()
)只能出现在 SELECT
或 ORDER BY
子句中。