ROW_NUMBER (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric
Menjumlahkan output dari kumpulan hasil. Lebih khusus lagi, mengembalikan jumlah baris berurutan dalam partisi kumpulan hasil, dimulai dari 1 untuk baris pertama di setiap partisi.
ROW_NUMBER dan RANK serupa. ROW_NUMBER angka semua baris secara berurutan (misalnya 1, 2, 3, 4, 5). RANK memberikan nilai numerik yang sama untuk ikatan (misalnya 1, 2, 2, 4, 5).
Catatan
ROW_NUMBER adalah nilai sementara yang dihitung saat kueri dijalankan. Untuk mempertahankan angka dalam tabel, lihat Properti IDENTITY dan URUTAN.
Sintaksis
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
PARTISI MENURUT value_expression
Membagi tataan hasil yang dihasilkan oleh klausul FROM menjadi partisi tempat fungsi ROW_NUMBER diterapkan. value_expression menentukan kolom tempat kumpulan hasil dipartisi. Jika PARTITION BY tidak ditentukan, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai grup tunggal. Untuk informasi selengkapnya, lihat Klausul OVER (Transact-SQL).
order_by_clause
Klausa ORDER BY menentukan urutan di mana baris ditetapkan uniknya ROW_NUMBER dalam partisi tertentu. Hal ini diperlukan. Untuk informasi selengkapnya, lihat Klausul OVER (Transact-SQL).
Jenis Kembalian
bigint
Keterangan Umum
Tidak ada jaminan bahwa baris yang dikembalikan oleh kueri yang menggunakan ROW_NUMBER() akan diurutkan sama persis dengan setiap eksekusi kecuali kondisi berikut ini benar.
Nilai kolom yang dipartisi unik.
ORDER BYNilai kolom unik.Kombinasi nilai kolom dan
ORDER BYkolom partisi unik.
ROW_NUMBER() bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.
Contoh
J. Contoh sederhana
Kueri berikut mengembalikan empat tabel sistem dalam urutan alfabet.
SELECT
name, recovery_model_desc
FROM sys.databases
WHERE database_id < 5
ORDER BY name ASC;
Berikut adalah hasil yang ditetapkan.
| nama | recovery_model_desc |
|---|---|
| master | SEDERHANA |
| model | FULL |
| msdb | SEDERHANA |
| tempdb | SEDERHANA |
Untuk menambahkan kolom nomor baris di depan setiap baris, tambahkan kolom dengan ROW_NUMBER fungsi , dalam hal ini bernama Row#. Anda harus memindahkan ORDER BY klausa ke OVER klausa.
SELECT
ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
name, recovery_model_desc
FROM sys.databases
WHERE database_id < 5;
Berikut adalah hasil yang ditetapkan.
| Baris# | nama | recovery_model_desc |
|---|---|---|
| 1 | master | SEDERHANA |
| 2 | model | FULL |
| 3 | msdb | SEDERHANA |
| 4 | tempdb | SEDERHANA |
PARTITION BY Menambahkan klausa pada recovery_model_desc kolom, akan memulai ulang penomoran saat recovery_model_desc nilai berubah.
SELECT
ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC)
AS Row#,
name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;
Berikut adalah hasil yang ditetapkan.
| Baris# | nama | recovery_model_desc |
|---|---|---|
| 1 | model | FULL |
| 1 | master | SEDERHANA |
| 2 | msdb | SEDERHANA |
| 3 | tempdb | SEDERHANA |
B. Mengembalikan nomor baris untuk tenaga penjualan
Contoh berikut menghitung nomor baris untuk tenaga penjualan di Adventure Works Cycles berdasarkan peringkat penjualan tahunan hingga saat ini.
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
Berikut adalah hasil yang ditetapkan.
Row FirstName LastName SalesYTD
--- ----------- ---------------------- -----------------
1 Linda Mitchell 4251368.54
2 Jae Pak 4116871.22
3 Michael Blythe 3763178.17
4 Jillian Carson 3189418.36
5 Ranjit Varkey Chudukatil 3121616.32
6 José Saraiva 2604540.71
7 Shu Ito 2458535.61
8 Tsvi Reiter 2315185.61
9 Rachel Valdez 1827066.71
10 Tete Mensa-Annan 1576562.19
11 David Campbell 1573012.93
12 Garrett Vargas 1453719.46
13 Lynn Tsoflias 1421810.92
14 Pamela Ansman-Wolfe 1352577.13
C. Mengembalikan subset baris
Contoh berikut menghitung nomor baris untuk semua baris dalam tabel dalam SalesOrderHeader urutan OrderDate dan hanya mengembalikan baris 50 ke 60 inklusif.
USE AdventureWorks2022;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
FROM Sales.SalesOrderHeader
)
SELECT SalesOrderID, OrderDate, RowNumber
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
D. Menggunakan ROW_NUMBER() dengan PARTISI
Contoh berikut menggunakan PARTITION BY argumen untuk mempartisi hasil kueri yang ditetapkan oleh kolom TerritoryName. Klausa ORDER BY yang ditentukan dalam OVER klausa mengurutkan baris di setiap partisi menurut kolom SalesYTD. Klausa ORDER BY dalam SELECT pernyataan mengurutkan seluruh hasil kueri yang ditetapkan oleh TerritoryName.
USE AdventureWorks2022;
GO
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC)
AS Row
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
ORDER BY TerritoryName;
Berikut adalah hasil yang ditetapkan.
FirstName LastName TerritoryName SalesYTD Row
--------- -------------------- ------------------ ------------ ---
Lynn Tsoflias Australia 1421810.92 1
José Saraiva Canada 2604540.71 1
Garrett Vargas Canada 1453719.46 2
Jillian Carson Central 3189418.36 1
Ranjit Varkey Chudukatil France 3121616.32 1
Rachel Valdez Germany 1827066.71 1
Michael Blythe Northeast 3763178.17 1
Tete Mensa-Annan Northwest 1576562.19 1
David Campbell Northwest 1573012.93 2
Pamela Ansman-Wolfe Northwest 1352577.13 3
Tsvi Reiter Southeast 2315185.61 1
Linda Mitchell Southwest 4251368.54 1
Shu Ito Southwest 2458535.61 2
Jae Pak United Kingdom 4116871.22 1
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
E. Mengembalikan nomor baris untuk tenaga penjualan
Contoh berikut mengembalikan ROW_NUMBER untuk perwakilan penjualan berdasarkan kuota penjualan yang ditetapkan.
-- Uses AdventureWorks
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC)
AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Berikut adalah kumpulan hasil parsial.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. Menggunakan ROW_NUMBER() dengan PARTISI
Contoh berikut menunjukkan penggunaan ROW_NUMBER fungsi dengan PARTITION BY argumen . Hal ini menyebabkan ROW_NUMBER fungsi menomori baris di setiap partisi.
-- Uses AdventureWorks
SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey
ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
LastName, SalesTerritoryKey AS Territory,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName, SalesTerritoryKey;
Berikut adalah kumpulan hasil parsial.
RowNumber LastName Territory SalesQuota
--------- ------------------ --------- -------------
1 Campbell 1 4,025,000.00
2 Ansman-Wolfe 1 3,551,000.00
3 Mensa-Annan 1 2,275,000.00
1 Blythe 2 11,162,000.00
1 Carson 3 12,198,000.00
1 Mitchell 4 11,786,000.00
2 Ito 4 7,804,000.00
Lihat Juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk