یک پرس و جویی که به طور متداول مورد استفاده قرار می گیرد و در Forum های مختلف پرسیده می شود مساله ی معروف Top N for each Group می باشد. روش های متنوعی برای حل این مساله وجود دارند که به بهترین آنها در این مقاله اشاره می کنم.
فرض کنید جدولی داریم که لیست مشتریان در آن ذخیره شده اند و در جدول دیگر سفارشات مربوط به مشتریان را نگهداری می کنیم. در نتیجه یک ارتباط 1 به N بین این دو جدول وجود دارد. در طول زمان مشتریان سفارشات مختلفی و بسیار انجام می دهند. هنگام نمایش این سفارشات ما علاقه داریم که تنها آخرین سفارش های هر مشتری را مشاهده کنیم نه تمام سفارشاتی که ممکن است در طول سالهای متوالی صورت گرفته باشد. در نتیجه به این پرس و جو نیاز خواهیم داشت.
توجه داشته باشید که ما به دنبال آخرین سفارش ایجاد شده در جدول نیستیم بلکه نیاز به آخرین سفارش هر مشتری داریم، در نتیجه روش TOP 1 ... ORDER BY data DESC جوابگوی نیاز ما نیست.
من از بانک Northwind برای گزارشاتم استفاده می کنم. فرض کنید می خواهیم 3 سفارش آخر هر مشتری را به نمایش در آوریم. تاریخ سفارش در ستون orderdate نگهداری می شود. و جدول مشتریان customers و جدول سفارشات orders نام دارند.
روش های موجود در SQL Server 2000
روش های موجود در SQL Server 2005
در صورت استفاده از نسخه ی 2005 به بعد بهترین و ساده ترین راه حل برای این مساله روش ROW_NUMBER می باشد. بدلیل اینکه این تنها روشی است که نیاز به تنها یکبار خواندن داده ها از جدول دارد ولی روش های دیگر بیش از یک بار نیاز به خواندن داده های جدول دارند.
روش اول
در این روش با کمک Correlated Subquery بطور پویا 3 سفارش آخر هر مشتری را بدست می آوریم.
--IN + TOP & ORDER BY
SELECT *
FROM Orders O
WHERE OrderID IN
(SELECT TOP 3 OrderID
FROM Orders
WHERE CustomerID = O.CustomerID
ORdER BY OrderDate DESC);
روش دوم
--Counting
SELECT *
FROM Orders O
WHERE (SELECT COUNT(*) AS RecID
FROM Orders
WHERE O.CustomerID = CustomerID
AND OrderDate >= O.OrderDate) <= 3;
روش سوم
SELECT O1.CustomerID, O1.OrderID, MAX(O1.OrderDate) AS OrderDate
FROM Orders O1
JOIN Orders O2 --Self Join
ON O1.CustomerID = O2.CustomerID
AND O1.OrderDate <= O2.OrderDate
GROUP BY O1.CustomerID, O1.OrderID
HAVING COUNT(*) <= 3;
روش چهارم
--ROW_NUMBR
SELECT *
FROM (SELECT *,
ROW_NUMBER() OVER(PARTITION BY Customerid
ORDER BY orderdate DESC) AS RecID
FROM Orders) D
WHERE RecID <= 3;
روش پنجم
--CROSS APPLY + TOP & ORDER BY
SELECT D.*
FROM Customers C
CROSS APPLY (SELECT TOP 3 *
FROM Orders
WHERE CustomerID = C.CustomerID
ORDER BY OrderDate DESC) D;
روش ششم
--CROSS APPLY + Counting
SELECT *
FROM Orders O
CROSS APPLY (SELECT COUNT(*) AS RecID
FROM Orders
WHERE O.CustomerID = CustomerID
AND OrderDate >= O.OrderDate) D
WHERE RecID <= 3;
مراجع
madhivanan (SQL Server MVP)
Inside MS SQL Server T-SQL: Querying By Itzik Ben Gan
30sharp.com