群組資料取 Top 1

bestlong

整理使用三種方法來取得資料： [Inner Join]、[SubQuery]、[ROW_NUMBER()]，檢視其執行計畫後，
成本最低是：[SubQuery]。

請參考以下的範例：

1. USE Northwind
   
2. GO
   
3. 
   
4. /*
   
5. 假設 ProductID 是自動編號，CategoryID 則是群組編號值，取得資料表每一個資料行。
   
6. */
   
7. 
   
8. -- Inner Join ， COST = 0.0170818
   
9. SELECT P1.ProductID, P1.ProductName, P1.SupplierID, P1.CategoryID, P1.QuantityPerUnit,
   
10. P1.UnitPrice, P1.UnitsInStock, P1.UnitsOnOrder, P1.ReorderLevel, P1.Discontinued
    
11. FROM Products P1 INNER JOIN Products P2
    
12. ON P1.CategoryID = P2.CategoryID
    
13. GROUP BY P1.ProductID, P1.ProductName, P1.SupplierID, P1.CategoryID, P1.QuantityPerUnit,
    
14. P1.UnitPrice, P1.UnitsInStock, P1.UnitsOnOrder, P1.ReorderLevel, P1.Discontinued
    
15. HAVING P1.ProductID = min(P2.ProductID)
    
16. 
    
17. -- SubQuery ， COST =  0.007844
    
18. SELECT P1.*
    
19. FROM Products P1
    
20. WHERE p1.ProductID =
    
21. (
    
22. SELECT min(p2.ProductID)
    
23. FROM Products P2
    
24. WHERE P1.CategoryID = P2.CategoryID
    
25. )
    
26. 
    
27. -- ROW_NUMBER() -- Support SQL Server 2005 ， COST =  0.0155255
    
28. SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
    
29. UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
    
30. FROM (
    
31. SELECT ROW_NUMBER() OVER(PARTITION BY CategoryID ORDER BY ProductID) 'rown',
    
32. *
    
33. FROM    Products ) AS TN
    
34. WHERE rown = 1

複製代碼

Best Regards
Derrick Chen 德瑞克