محتویات سایت
        برچسب های محبوب 








 
   آخرین سفارش هر مشتری (Top N for each Group)
  Top N for Each Group یک مساله ی کلاسیک و عمومی است که در SQL Server 2005 روشهای متنوعی برای حل آن وجود دارد. که در این مقاله با آنها آشنا خواهید شد.
   SQL Server
   ۱۵۶۳۱
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۸۹/۲/۱۲
نسخه قابل چاپ نسخه قابل چاپ

یک پرس و جویی که به طور متداول مورد استفاده قرار می گیرد و در Forum های مختلف پرسیده می شود مساله ی معروف Top N for each Group می باشد. روش های متنوعی برای حل این مساله وجود دارند که به بهترین آنها در این مقاله اشاره می کنم.

فرض کنید جدولی داریم که لیست مشتریان در آن ذخیره شده اند و در جدول دیگر سفارشات مربوط به مشتریان را نگهداری می کنیم. در نتیجه یک ارتباط 1 به N بین این دو جدول وجود دارد. در طول زمان مشتریان سفارشات مختلفی و بسیار انجام می دهند. هنگام نمایش این سفارشات ما علاقه داریم که تنها آخرین سفارش های هر مشتری را مشاهده کنیم نه تمام سفارشاتی که ممکن است در طول سالهای متوالی صورت گرفته باشد. در نتیجه به این پرس و جو نیاز خواهیم داشت.

توجه داشته باشید که ما به دنبال آخرین سفارش ایجاد شده در جدول نیستیم بلکه نیاز به آخرین سفارش هر مشتری داریم، در نتیجه روش TOP 1 ... ORDER BY data DESC جوابگوی نیاز ما نیست.

من از بانک Northwind برای گزارشاتم استفاده می کنم. فرض کنید می خواهیم 3 سفارش آخر هر مشتری را به نمایش در آوریم. تاریخ سفارش در ستون orderdate نگهداری می شود. و جدول مشتریان customers و جدول سفارشات orders نام دارند.

روش های موجود در SQL Server 2000

  • IN() + TOP & ORDER BY

  • Standard Ranking (Counting)

  • Standard Ranking (Self Join)

روش های موجود در SQL Server 2005

  • ROW_NUMBER

  • CROSS APPLY + TOP & ORDER BY

  • CROSS APPLY + Standard Ranking

در صورت استفاده از نسخه ی 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 DESCAS 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