搜尋

首頁  >  問答  >  主體

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

<p>我有一個文件表格(這裡是簡化版本):</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>rev</th> <th>content</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>1</td> <td>...</td> </tr> <tr> <td>2</td> <td>1</td> <td>...</td> </tr> <tr> <td>1</td> <td>2</td> <td>...</td> </tr> <tr> <td>1</td> <td>3</td> <td>...</td> </tr> </tbody> </table> <p>如何選擇每個id的一行,並且只選擇最大的rev? </p><p> 根據上述數據,結果應該包含兩行:<code>[1, 3, ...]</code> 和 <code>[2, 1, ..]</code>。我正在使用<strong><em>MySQL</em></strong>。 </p> <p>目前,我在<code>while</code>循環中使用檢查來檢測並覆蓋結果集中的舊revs。但是這是實現結果的唯一方法嗎?難道沒有<strong>SQL</strong>的解決方案嗎? </p>
P粉752479467P粉752479467458 天前499

全部回覆(2)我來回復

  • P粉667649253

    P粉6676492532023-08-23 09:25:35

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

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

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

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

    回覆
    0
  • P粉517475670

    P粉5174756702023-08-23 09:14:22

    乍看之下...

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

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

    事情從來都不是那麼簡單,對嗎?

    我剛剛注意到你還需要content列。

    這是SQL中一個非常常見的問題:根據某個分組標識符找到某一列中最大值對應的完整資料。在我的職業生涯中,我聽到了很多這樣的問題。實際上,在我目前的工作技術面試中,我就回答了這個問題之一。

    這個問題其實非常常見,以至於Stack Overflow社群創建了一個專門處理這類問題的標籤:

    基本上,你有兩種方法來解決這個問題:

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

    在這個方法中,你先在一個子查詢中找到group-identifier, max-value-in-group(已經在上面解決了)。然後,你將你的表與子查詢連接,使用group-identifiermax-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-identifiermax-value-in-group,那麼這兩種方法都會在結果中包含這兩行。

    這兩種方法都與SQL ANSI相容,因此無論你使用的是哪種RDBMS,都可以使用這兩種方法,不受其「風格」的影響。

    這兩種方法都非常高效,但是具體效果可能會有所不同(RDBMS、資料庫結構、索引等)。因此,在選擇其中一種方法時,請進行基準測試。並確保選擇對你來說最有意義的方法。

    回覆
    0
  • 取消回覆