[T-SQL]分組排名(SUBSQERY、CTE及用TOP))

        如何從銷售訂單資訊[SalesOrderHeader]資料表中，找出每個顧客[CustomerID]其應付總額[TotalDue]前三高的訂單[SalesOrderID]，並顯示名次？呈現結果如下

        注意應付總額一樣的也要列喔

        小弟又無聊在訓練水平思考的方法，逼自己寫網誌，以下列出三種寫法

 

USE [AdventureWorks2008R2]

GO

--1.SQL 2005之前的解法(使用SUBQUERY)

SELECT * FROM (

   SELECT S1.CustomerID,

      S1.SalesOrderID,

      S1.TotalDue,

      (SELECT COUNT(*) FROM Sales.SalesOrderHeader

          WHERE CustomerID = S1.CustomerID AND TotalDue > S1.TotalDue

          OR (TotalDue = S1.TotalDue AND S1.SalesOrderID = SalesOrderID)

      )  AS 'CNT'

   FROM Sales.SalesOrderHeader S1

) A

WHERE A.CNT <= 3

ORDER BY A.CustomerID, A.CNT

--2.SQL 2005之後的解法(使用CTE)

;WITH CTE (CustomerID,SalesOrderID,TotalDue,CNT) AS

(

   SELECT CustomerID,

      SalesOrderID,

      TotalDue,

      RANK() OVER(PARTITION BY CustomerID ORDER BY TotalDue DESC)AS 'CNT'

   FROM Sales.SalesOrderHeader

)

SELECT CustomerID,SalesOrderID,TotalDue,CNT FROM CTE WHERE   CNT <=3

--3.SQL 2005之後的解法(用TOP，但得使用Table Valued Functions配合CROSS APPLY囉)

--ａ.先建Table Valued Functions

CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS INT, @n AS INT)

  RETURNS TABLE

AS

RETURN

    SELECT TOP(@n) WITH ties CustomerID,SalesOrderID,TotalDue,RANK() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) AS 'CNT' FROM Sales.SalesOrderHeader

  WHERE CustomerID = @custid

  ORDER BY TotalDue DESC

GO

--ｂ.使用CROSS APPLY

SELECT  C.CustomerID,

   O.SalesOrderID,

   O.TotalDue,

   O.CNT

FROM

   Sales.Customer AS C

CROSS APPLY

   dbo.fn_GetTopOrders(C.CustomerID, 3) AS O

ORDER BY

   CustomerID ASC, TotalDue DESC

        最後大家會問哪個寫法較好啊？小弟三種寫法各執行三次，比較執行時間，結果如下圖，用CTE最佳囉！