如何使用SQL檢索具有最高值的列中的行 [重複]

Question

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

id	轉	內容
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

乍看之下...

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