問題描述

我有一個關(guān)于 T-SQL 和 SQL Server 的問題.

I have a question related to T-SQL and SQL Server.

假設(shè)我有一個包含 2 列的 Orders 表:

Let's say I have a table Orders with 2 columns:

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

我想要每個產(chǎn)品的第一個訂單的日期，所以我執(zhí)行這種類型的查詢:

I want the date of the first order for every product, so I perform this type of query:

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

我在 ProductId 上有一個索引，包括列 CustomerId 和 Date 以加快查詢速度 (IX_Orders>).查詢計劃看起來像是對 IX_Orders 的非聚集索引掃描，然后是流聚合(由于索引沒有排序).

I have an index on ProductId, including the columns CustomerId and Date to speed up the query (IX_Orders). The query plan looks like a non-clustered index scan on IX_Orders, followed by a stream aggregate (no sort thanks to the index).

現(xiàn)在我的問題是我還想檢索與每個產(chǎn)品的第一個訂單相關(guān)聯(lián)的 CustomerId(產(chǎn)品 26 于 25 日星期二由客戶 12 首次訂購).棘手的部分是我不希望在執(zhí)行計劃中有任何內(nèi)部循環(huán)，因為這意味著表中每個 ProductId 的額外讀取，這是非常低效的.

Now my problem is that I also want to retrieve the CustomerId associated with the first order for each product (Product 26 was first ordered on Tuesday 25, by customer 12). The tricky part is that I don't want any inner loop in the execution plan, because it would mean an additional read per ProductId in the table, which is highly inefficient.

這應(yīng)該可以使用相同的非聚集索引掃描，然后是流聚合，但是我似乎找不到可以做到這一點的查詢.有什么想法嗎?

This should just be possible using the same non-clustered index scan, followed by stream aggregates, however I can't seem to find a query that would do that. Any idea?

謝謝

推薦答案

這將處理具有重復(fù)日期的產(chǎn)品:

this will handle products that have duplicate dates:

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

這將返回相同的結(jié)果，只需使用與上述代碼相同的聲明和插入:

this will return the same results, just use the same declare and inserts as the above code:

;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

您可以嘗試每個版本，看看哪個版本運行得更快...

You can try out each version to see which will run faster...

這篇關(guān)于查找與 Min/Max 關(guān)聯(lián)的行，沒有內(nèi)部循環(huán)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！