在PostgreSQL中,有Limit
和Offset
关键字,它们可以非常容易地对结果集进行分页。
SQL Server的等效语法是什么?
发布于 2010-01-26 04:40:27
您可以在公用表表达式中使用ROW_NUMBER来实现这一点。
;WITH My_CTE AS
(
SELECT
col1,
col2,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
SELECT
col1,
col2
FROM
My_CTE
WHERE
row_number BETWEEN @start_row AND @end_row
发布于 2017-07-27 11:29:40
-- @RowsPerPage can be a fixed number and @PageNumber number can be passed
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 2
SELECT *
FROM MemberEmployeeData
ORDER BY EmployeeNumber
OFFSET @PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
发布于 2013-02-27 07:32:28
在Aaronaught的解决方案上添加了一点变化,我通常将页码(@PageNum)和页面大小(@PageSize)参数化。这样,每个页面单击事件只会发送请求的页码和可配置的页面大小:
begin
with My_CTE as
(
SELECT col1,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
select * from My_CTE
WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1)
AND @PageNum * @PageSize
end
https://stackoverflow.com/questions/2135418
复制相似问题