我有一张文件表(此处为简化版):

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

如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。

目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?


当前回答

按相反顺序对rev字段进行排序,然后按id进行分组,每个分组的第一行是rev值最高的一行。

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

测试时间:http://sqlfiddle.com/具有以下数据

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

这在MySql 5.5和5.6中给出了以下结果

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

其他回答

此解决方案仅从YourTable中进行一次选择,因此速度更快。根据sqlfiddle.com上的测试,它只适用于MySQL和SQLite(用于SQLite删除DESC)。也许可以调整它以适用于我不熟悉的其他语言。

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

不是mySQL,但对于其他发现此问题并使用SQL的人,另一种解决最大的每组问题的方法是在MS SQL中使用交叉应用

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

下面是SqlFiddle中的一个示例

这是另一个解决方案,希望它能帮助某人

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

我的偏好是使用尽可能少的代码。。。

您可以使用IN试试看:

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

在我看来,这不那么复杂。。。易于阅读和维护。

我不能保证它的性能,但这里有一个受Microsoft Excel限制启发的技巧。它有一些好的特点

好东西

即使有平局,它也应该强制返回一个“最大记录”(有时有用)它不需要联接

接近,接近

它有点难看,需要您了解rev列的有效值范围。假设我们知道rev列是一个介于0.00和999之间的数字,包括小数,但小数点右侧只有两位数字(例如,34.17是一个有效值)。

这件事的要点是,通过将主比较字段与所需数据串接/打包,可以创建一个单独的合成列。通过这种方式,您可以强制SQL的MAX()聚合函数返回所有数据(因为它已打包到一列中)。然后您必须解压缩数据。

下面是上面用SQL编写的示例的外观

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

包装开始时,将rev列强制为已知字符长度的数字,而不考虑rev的值,例如

3.2变为1003.20157变为1057.001923.88变为1923.881

如果你做得对,两个数字的字符串比较应该产生与两个数字数字比较相同的“最大值”,并且很容易使用substring函数转换回原始数字(这在任何地方都可以使用一种或另一种形式)。