搜索

首页  >  问答  >  正文

只选择具有最大值的行的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 天前500

全部回复(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
  • 取消回复