排名函数:RANK、DENSE_RANK、NTILE、ROW_NUMBER值得一用 (Transact-SQL)

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

Transact-SQL 提供下列排名函数: 都需要第二个参数OVER ( [ <partition_by_clause> ] < order_by_clause > )

RANK

NTILE

DENSE_RANK

ROW_NUMBER

示例

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


USE AdventureWorks;   
GO
SELECT c.FirstName, c.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 s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;

下面是结果集:

FirstName LastName Row Number Rank Dense Rank Quartile SalesYTD PostalCode

Maciej

Dusza

1

1

1

1

4557045

98027

Shelley

Dyck

2

1

1

1

5200475

98027

Linda

Ecoffey

3

1

1

1

3857164

98027

Carla

Eldridge

4

1

1

1

1764939

98027

Carol

Elliott

5

1

1

2

2811013

98027

Jauna

Elson

6

6

2

2

3018725

98055

Michael

Emanuel

7

6

2

2

3189356

98055

Terry

Eminhizer

8

6

2

3

3587378

98055

Gail

Erickson

9

6

2

3

5015682

98055

Mark

Erickson

10

6

2

3

3827950

98055

Martha

Espinoza

11

6

2

4

1931620

98055

Janeth

Esteves

12

6

2

4

2241204

98055

Twanna

Evans

13

6

2

4

1758386

98055

posted @ 2011-03-29 15:42  great wang  阅读(205)  评论(0编辑  收藏  举报