以Over子句代替子查尋，SQL將更有效率－忙裡偷閒的部落格

以往在撰寫報表程式時，時常需要大量的運用子查詢及聚合函數(SUM、AYG、COUNT)，SQL寫的又臭又長，
例如計算總訂單量，會這樣寫:
SELECT a.SalesOrderID, a.ProductID, a.OrderQty
    ,(select sum(b.OrderQty) FROM Sales.SalesOrderDetail b
    where a.SalesOrderID=b.SalesOrderID GROUP BY b.SalesOrderID) As 'Total'
    ,(select avg(b.OrderQty) FROM Sales.SalesOrderDetail b
    where a.SalesOrderID=b.SalesOrderID GROUP BY b.SalesOrderID) AS 'Avg'
    ,(select COUNT(b.OrderQty) FROM Sales.SalesOrderDetail b
    where a.SalesOrderID=b.SalesOrderID GROUP BY b.SalesOrderID) AS 'Count'
    ,(select min(b.OrderQty) FROM Sales.SalesOrderDetail b
where a.SalesOrderID=b.SalesOrderID GROUP BY b.SalesOrderID) AS 'Min'
FROM Sales.SalesOrderDetail a FROM a.SalesOrderID in(43659,43664)
GROUP BY a.SalesOrderID, a.ProductID, a.OrderQty

無意中在微軟的技術網站裡發現這篇文章，上述範例用OVER子句去寫，更簡潔了，並且能提高效率。
以上範例如用OVER子句來寫會是這樣 :

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);

如獲至寶，興奮之餘，馬上作筆記@@!