SQL 僅選擇列上具有最大值的行

Question

我有這個文件表格（這裡是簡化版本）：

id	轉	內容
1	1	...
2	1	...
1	2	...
1	3	...

如何為每個 ID 選擇一行且僅選擇最大的轉速？

使用上述數據，結果應包含兩行：[1, 3, ...] 和 [2, 1, ..]。我正在使用MySQL。

目前，我在 while 循環中使用檢查來檢測並覆蓋結果集中的舊轉速。但這是實現這結果的唯一方法嗎？沒有SQL解決方案嗎？

Answer 1

我的偏好是使用盡可能少的程式碼...

您可以使用IN來做到這一點 試試這個：

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

在我看來，它沒那麼複雜......更容易閱讀和維護。

Answer 2

乍看之下...

您所需要的只是一個帶有 MAX 聚合函數的 GROUP BY 子句：

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

事情從來沒有那麼簡單，不是嗎？

我剛剛注意到您還需要 content 欄位。

這是 SQL 中一個非常常見的問題：在每個群組標識符的列中尋找具有某個最大值的行的全部資料。在我的職業生涯中我常聽到這樣的說法。事實上，這是我在目前工作的技術面試中回答的問題之一。

實際上，Stack Overflow 社群創建了一個標籤來處理這樣的問題：greatest-n-per-group。

基本上，您有兩種方法可以解決該問題：

使用簡單的group-identifier, max-value-in-group子查詢連線

在這個方法中，您首先在子查詢中找到group-identifier, max-value-in-group（上面已解決）。然後，將表格連接到子查詢，並在 group-identifier 和 max-value-in-group 上相等：

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

與 self 左連接，調整連接條件與篩選器

在這種方法中，您將表格與其自身左連接。平等存在於group-identifier中。然後，2個聰明的舉動：

1. 第二個連接條件是左側值小於右側值
2. 當您執行第 1 步時，實際具有最大值的行將在右側顯示 NULL（這是一個 LEFT JOIN，還記得嗎？） 。然後，我們過濾連線結果，僅顯示右側為 NULL 的行。

所以你最終會得到：

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

結論

兩種方法都會帶來完全相同的結果。

如果您有兩行 group-identifier 具有 max-value-in-group，則這兩行都會出現在兩種方法的結果中。

這兩種方法都相容於 SQL ANSI，因此，無論其「風格」如何，都可以與您最喜歡的 RDBMS 配合使用。

這兩種方法對效能也都很友好，但是您的情況可能會有所不同（RDBMS、資料庫結構、索引等）。因此，當您選擇一種方法而不是另一種方法時，基準。並確保您選擇對您最有意義的一個。