次序函數 (Transact-SQL)
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲
排名函數會傳回資料分割中每個資料列的次序值。 根據所用的函數而定，有些資料列可能會收到與其他資料列相同的值。 排名函數不具決定性。
Transact-SQL 會提供下列次序函數：
範例
下列範例顯示在相同查詢中使用的四個排名函數。 如需特定函數的範例，請參閱各個排名函數。
USE AdventureWorks2022;
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 | 姓氏 | 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 | 7 | 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 |