SQL Server 中ROW_NUMBER() OVER基本用法

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 兼容而提供的。

对比分析:

 

 

1~100

5001~5100

9900~10000

估计行数

OFFSET FETCH

开销占比

49%

84%

90%

100

ROW_NUMBER

开销占比

51%

16%

10%

9

ROW_NUMBER 在 编译内存,CPU 比 OFFSET FETCH 多。

上面统计中:OFFSET FETCH 查询的记录在表中越靠后,开销反而更大,而这个的估计行数是准确的。

上一篇:团队作业4--第一次项目冲刺2(Alpha版本)


下一篇:【RMQ】洛谷P3379 RMQ求LCA