排名函数 (Transact-SQL)

排名函数为分区中的每一行返回一个排名值。 根据所用函数的不同,某些行可能与其他行接收到相同的值。 排名函数具有不确定性。

Transact-SQL 提供下列排名函数:

RANK

NTILE

DENSE_RANK

ROW_NUMBER

示例

以下示例显示了用在同一查询中的四个排名函数。 有关函数特定的示例,请参阅每个排名函数。

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"
    ,RANK() OVER (ORDER BY a.PostalCode) AS Rank
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile
    ,s.SalesYTD
    ,a.PostalCode
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

下面是结果集:

FirstName

LastName

Row Number

Rank

Dense Rank

Quartile

SalesYTD

PostalCode

Michael

Blythe

1

1

1

1

4557045.0459

98027

Linda

Mitchell

2

1

1

1

5200475.2313

98027

Jillian

Carson

3

1

1

1

3857163.6332

98027

Garrett

Vargas

4

1

1

1

1764938.9859

98027

Tsvi

Reiter

5

1

1

2

2811012.7151

98027

Shu

Ito

6

6

2

2

3018725.4858

98055

José

Saraiva

7

6

2

2

3189356.2465

98055

David

Campbell

8

6

2

3

3587378.4257

98055

Tete

Mensa-Annan

9

6

2

3

1931620.1835

98055

Lynn

Tsoflias

10

6

2

3

1758385.926

98055

Rachel

Valdez

11

6

2

4

2241204.0424

98055

Jae

Pak

12

6

2

4

5015682.3752

98055

Ranjit

Varkey Chudukatil

13

6

2

4

3827950.238

98055

请参阅

参考

内置函数 (Transact-SQL)

OVER 子句 (Transact-SQL)