只選擇具有最大值的行的SQL查詢

Question

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

id	rev	content
1	1	...
2	1	...
1	2	...
1	3	...

如何選擇每個id的一行，並且只選擇最大的rev？

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

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

Answer 1

我比較喜歡盡量少使用程式碼...

你可以使用IN來實現 試試這個：

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

在我看來，這樣比較簡單...比較易讀和維護。

Answer 2

乍看之下...

你只需要在GROUP BY子句中使用MAX聚合函數：

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

使用自連接進行左連接，調整連接條件和篩選條件

在這種方法中，你將表格與自身進行左連接。等值連接在group-identifier上。然後，有兩個巧妙的步驟：

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、資料庫結構、索引等）。因此，在選擇其中一種方法時，請進行基準測試。並確保選擇對你來說最有意義的方法。