ROW_NUMBER trong sql server

Hàm ROW_NUMBER được đưa vào từ bản 2005, dùng để trả về một con số tuần tự gắn với mỗi bản ghi (do đó có tên như vậy, số của bản ghi). Hàm này giúp giải quyết một số bài toán dễ dàng hơn. Bài viết này giới thiệu hai ứng dụng của hàm ROW_NUMBER, nếu bạn có sử dụng hàm này vào những tình huống khác xin hãy bổ sung ở phần comment.

1. Phân trang: Đây là ứng dụng khá phổ biến của hàm ROW_NUMBER và chỉ khai thác một dạng thức đơn giản của hàm. Trong ví dụ dưới đây tôi dựa vào dữ liệu từ database AdventureWorks. Giả sử tôi có một trang web hiển thị các đơn bán hàng trong tháng 6/2004. Vì có khá nhiều đơn hàng (hơn 2000) nên tôi muốn chia làm nhiều trang với mỗi trang chỉ hiển thị 100 đơn hàng. Khi muốn liệt kê đơn hàng cho trang thứ hai (đơn hàng từ 101-200) tôi có thể dùng code như sau:

SELECT * 
FROM(
	SELECT SalesOrderID, CustomerID, OrderDate, ROW_NUMBER() OVER(ORDER BY OrderDate) RN
	FROM Sales.SalesOrderHeader
	WHERE OrderDate BETWEEN '2004-06-01' AND '2004-06-30'
) a
WHERE RN BETWEEN 101 AND 200
ORDER BY OrderDate

Cú pháp tổng quát của hàm là ROW_NUMBER() OVER(), rườm rà hơn các hàm thông thường. Trong ví dụ trên, “ROW_NUMBER() OVER(ORDER BY OrderDate)” nghĩa là gán một con số tăng tuần tự cho mỗi bản ghi theo thứ tự của OrderDate, bắt đầu từ 1. Trong trường hợp tổng quát, bạn có thể viết một thủ tục và nhận các tham số vào là kích thước trang và số trang để tăng khả năng tùy biến:

ALTER PROC dbo.pGetOrder
@PageSize INT,
@PageNum INT
AS
SELECT * 
FROM(
	SELECT SalesOrderID, CustomerID, OrderDate, ROW_NUMBER() OVER(ORDER BY OrderDate) RN
	FROM Sales.SalesOrderHeader
	WHERE OrderDate BETWEEN '2004-06-01' AND '2004-06-30'
) a
WHERE RN BETWEEN (@PageNum-1)*@PageSize+1 AND @PageNum*@PageSize
ORDER BY OrderDate 
GO
 
-- ví dụ: 50 bản ghi mỗi trang và lấy cho trang 3
EXEC dbo.pGetOrder 50, 3

Ở bản SQL Server 2000, giải pháp cho bài toán trên thường là SELECT vào một bảng tạm vốn đã có một trường IDENTITY, và sau đó SELECT từ bảng tạm với điều kiện lọc trường IDENTITY nằm trong khoảng cần trả về. Cách làm dùng hàm ROW_NUMBER ở trên gọn gàng hơn một chút, tuy nhiên về hiệu năng tôi cho là cả hai cách đều giống nhau.

2. Lấy về TOP bản ghi theo chủng loại: Cũng dựa vào ví dụ trên, giả sử nay tôi muốn lấy về top 5 đơn hàng có trị giá lớn nhất tại mỗi vùng (TerritoryID). Như vậy nếu có 10 vùng thì tôi sẽ nhận được 50 bản ghi trong đó mỗi vùng chiếm 5 bản ghi chứa các đơn hàng có giá trị lớn nhất của vùng đó:

SELECT *
FROM (
	SELECT TerritoryID, SalesOrderID, TotalDue, ROW_NUMBER() OVER(PARTITION BY TerritoryID ORDER BY TotalDue DESC) RN
	FROM Sales.SalesOrderHeader
	WHERE OrderDate BETWEEN '2004-06-01' AND '2004-06-30') a
WHERE RN <=5

Ở ví dụ trên hàm có sử dụng thêm mệnh đề “PARTITION BY TerritoryID” để chỉ định rằng giá trị chỉ tăng tuần tự trong phạm vi của TerritoryID, khi gặp một TerritoryID mới thì reset lại giá trị từ 1. Còn mệnh đề “ORDER BY TotalDue DESC” đảm nhiệm sắp xếp các bản ghi theo thứ tự TotalDue giảm dần. Do đó bản ghi có TotalDue lớn nhất được gán là 1, bản ghi tiếp theo là 2… Đến khi gặp TerritoryID mới nó lại lặp lại quá trình đó và bản ghi có TotalDue lớn nhất ở TerritoryID mới này lại được gán là 1 và cứ tiếp tục như thế. Ở đây bạn mới thấy thế mạnh của hàm ROW_NUMBER. Trước kia khi muốn làm một điều tương tự, chắc bạn vẫn phải SELECT vào một bảng tạm rồi dùng một thuật toán rất loằng ngoằng để gán số thứ tự cho các bản ghi như vậy.

Nguồn: sqlviet[dot]com