共用方式為


次序函數 (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)