群組資料取 Top 1 - bestlong 怕失憶論壇

USE Northwind
GO
/*
假設 ProductID 是自動編號，CategoryID 則是群組編號值，取得資料表每一個資料行。
*/
-- Inner Join ， COST = 0.0170818
SELECT P1.ProductID, P1.ProductName, P1.SupplierID, P1.CategoryID, P1.QuantityPerUnit,
P1.UnitPrice, P1.UnitsInStock, P1.UnitsOnOrder, P1.ReorderLevel, P1.Discontinued
FROM Products P1 INNER JOIN Products P2
ON P1.CategoryID = P2.CategoryID
GROUP BY P1.ProductID, P1.ProductName, P1.SupplierID, P1.CategoryID, P1.QuantityPerUnit,
P1.UnitPrice, P1.UnitsInStock, P1.UnitsOnOrder, P1.ReorderLevel, P1.Discontinued
HAVING P1.ProductID = min(P2.ProductID)
-- SubQuery ， COST = 0.007844
SELECT P1.*
FROM Products P1
WHERE p1.ProductID =
(
SELECT min(p2.ProductID)
FROM Products P2
WHERE P1.CategoryID = P2.CategoryID
)
-- ROW_NUMBER() -- Support SQL Server 2005 ， COST = 0.0155255
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY CategoryID ORDER BY ProductID) 'rown',
*
FROM Products ) AS TN
WHERE rown = 1

複製代碼