SQL Server 中ROW_NUMBER() OVER基本用法

最新推荐文章于 2026-04-20 16:55:23 发布

转载最新推荐文章于 2026-04-20 16:55:23 发布 · 371 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：http://www.cnblogs.com/zhaoshujie/p/9594721.html

标签

#数据库

本文详细介绍了四种SQL分页查询的方法：不推荐的不能排序法、SQL2000临时表法、SQL2005/2008的Row_Number法以及SQLServer2012后的OFFSET/FETCHNEXT法，并对比了各种方法的性能开销。

1、不能排序法

SELECT TOP 10 *
FROM table1
WHERE id NOT IN (
    SELECT TOP 开始的位置 id
    FROM table1
)

2、SQL 2000 临时表法

DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000, @End = 14050

CREATE TABLE #employees (
    RowNumber INT IDENTITY (1, 1),
    LastName VARCHAR(100),
    FirstName VARCHAR(100),
    EmailAddress VARCHAR(100)
)

INSERT INTO #employees (LastName, FirstName, EmailAddress)
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
SELECT LastName, FirstName, EmailAddress
FROM #employees
WHERE RowNumber > @Start
    AND RowNumber <= @End

DROP TABLE #employees

3、SQL 2005/2008 Row_Number法

DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000, @End = 14050

SELECT LastName, FirstName, EmailAddress
FROM (
    SELECT LastName, FirstName, EmailAddress, ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
    FROM Employee
) EmployeePage
WHERE RowNumber > @Start
    AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

4、SQL SERVER 2012以后 OFFSET/FETCH NEXT法

SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS FETCH NEXT 50 ROWS ONLY;

语法说明：

ORDER BY ORDER_BY_EXPRESSION
    [ COLLATE COLLATION_NAME ] 
    [ ASC | DESC ] 
    [ ,...N ] 
[ <OFFSET_FETCH> ]
 
<OFFSET_FETCH> ::=
{ 
    OFFSET { INTEGER_CONSTANT | OFFSET_ROW_COUNT_EXPRESSION } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {INTEGER_CONSTANT | FETCH_ROW_COUNT_EXPRESSION } { ROW | ROWS } ONLY
    ]
}

--FIRST 和 NEXT 是同义词，是为了与 ANSI 兼容而提供的。
--ROW 和 ROWS 是同义词，是为了与 ANSI 兼容而提供的。