我需要在网页上编写分页代码,通常使用mysql可以简单地使用SELECT with LIMIT 60, 20
来选择从第60个位置开始的20行。
现在我正在使用SQL Server,但需要解决这个问题 - 我知道TOP语法不适用,我也听说过SQL Server中的ROW_NUMBER()
函数,但我找到的示例不够清晰 - 请帮助我在测试表“cars”上简单地实现。
select * from cars limit 20, 10
我需要在网页上编写分页代码,通常使用mysql可以简单地使用SELECT with LIMIT 60, 20
来选择从第60个位置开始的20行。
现在我正在使用SQL Server,但需要解决这个问题 - 我知道TOP语法不适用,我也听说过SQL Server中的ROW_NUMBER()
函数,但我找到的示例不够清晰 - 请帮助我在测试表“cars”上简单地实现。
select * from cars limit 20, 10
ROW_NUMBER()不能在where子句中使用,所以您必须使用单独的select语句:
select *
from (select row_number() over (ORDER BY cars.CarId) as Row, *
from cars
) temp
where Row between 20 and 29
CREATE PROCEDURE Paging_Customers
(
@SelectedPage int,
@PageSize int
)
AS
BEGIN
WITH CTE_Customers(PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country)
AS
(
SELECT CEILING((ROW_NUMBER() OVER
(ORDER BY CompanyName ASC
AS PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country
FROM Customers
)
SELECT *
FROM CTE_Customers WHERE PageNumber = @SelectedPage
END
然后,我们使用这一简单行调用该过程(对于第三页和每页十行):
EXEC Paging_Customers 3, 10
-- Sample data
;with cars(Name) as
(
select 'SAAB' union all
select 'Volvo' union all
select 'Opel' union all
select 'Ford'
)
-- Query using row_number
select *
from
(
select *,
row_number() over(order by Name) as rn
from cars
) as C
where C.rn between 2 and 3
结果:
Name rn
---- --
Opel 2
SAAB 3
试试这个:
SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (ORDER BY col1) AS row FROM sys.databases) table1
WHERE row > 20 and row <= 30
在SO上提供了更多答案这里。