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

Question

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

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

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

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

目前，我在while循環中使用檢查來檢測和覆蓋結果集中的舊rev。但是這是實現結果的唯一方法嗎？難道沒有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

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

在這種方法中，你將表格與自身進行左連接。等值連線在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、資料庫結構、索引等）。所以當你選擇其中一個方法時，請進行基準測試。並確保選擇對你來說最有意義的方法。