# 查找與最小/最大值相關聯的行，無內循環

• ProductId int
• CustomerId int
• 日期日期時間

SELECT ProductId, MIN(Date) AS FirstOrder
FROM Orders
GROUP BY ProductId


## 最佳答案

DECLARE @Orders table (ProductId int
,CustomerId int
,Date datetime
)

INSERT INTO @Orders VALUES (1,1,'20090701')
INSERT INTO @Orders VALUES (2,1,'20090703')
INSERT INTO @Orders VALUES (3,1,'20090702')
INSERT INTO @Orders VALUES (1,2,'20090704')
INSERT INTO @Orders VALUES (4,2,'20090701')
INSERT INTO @Orders VALUES (1,3,'20090706')
INSERT INTO @Orders VALUES (2,3,'20090704')
INSERT INTO @Orders VALUES (4,3,'20090702')
INSERT INTO @Orders VALUES (5,5,'20090703')  --duplicate dates for product #5
INSERT INTO @Orders VALUES (5,1,'20090703')  --duplicate dates for product #5
INSERT INTO @Orders VALUES (5,5,'20090703')  --duplicate dates for product #5

;WITH MinOrders AS
(SELECT
o.ProductId, o.CustomerId, o.Date
,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue
FROM @Orders o
INNER JOIN (SELECT
ProductId
,MIN(Date) MinDate
FROM @Orders
GROUP BY ProductId
) dt ON o.ProductId=dt.ProductId AND o.Date=dt.MinDate
)
SELECT
m.ProductId, m.CustomerId, m.Date
FROM MinOrders  m
WHERE m.RankValue=1
ORDER BY m.ProductId, m.CustomerId


;WITH MinOrders AS
(SELECT
o.ProductId, o.CustomerId, o.Date
,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue
FROM @Orders o
)
SELECT
m.ProductId, m.CustomerId, m.Date
FROM MinOrders  m
WHERE m.RankValue=1
ORDER BY m.ProductId, m.CustomerId