我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
当前回答
我喜欢使用基于NOT EXIST的解决方案来解决这个问题:
SELECT
id,
rev
-- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
这将选择组中具有最大值的所有记录,并允许您选择其他列。
其他回答
下面是另一个解决方案,用于仅检索具有该字段最大值的字段的记录。这适用于我工作的平台SQL400。在本例中,字段FIELD5中具有最大值的记录将由以下SQL语句检索。
SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
FROM MYFILE A
WHERE RRN(A) IN
(SELECT RRN(B)
FROM MYFILE B
WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
ORDER BY B.FIELD5 DESC
FETCH FIRST ROW ONLY)
我喜欢使用基于NOT EXIST的解决方案来解决这个问题:
SELECT
id,
rev
-- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
这将选择组中具有最大值的所有记录,并允许您选择其他列。
乍一看。。。
您只需要一个带有MAX聚合函数的GROUP BY子句:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
从来没有这么简单,是吗?
我刚刚注意到你也需要内容栏。
这是SQL中一个非常常见的问题:在每个组标识符的列中查找具有某个最大值的行的整个数据。在我的职业生涯中我听过很多。事实上,这是我在当前工作的技术面试中回答的问题之一。
事实上,StackOverflow社区创建了一个标签来处理这样的问题是非常常见的:每个组中最优秀的一个。
基本上,有两种方法可以解决这个问题:
使用简单组标识符连接,组子查询中的最大值
在这种方法中,首先在子查询中找到组标识符,即组中的最大值(上面已经解决)。然后,将表连接到子查询,组标识符和组中的最大值相等:
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
左侧使用自身连接,调整连接条件和过滤器
在这种方法中,您可以将表与自身连接起来。组标识符中包含相等项。然后,两个聪明的动作:
第二个连接条件的左侧值小于右侧值执行步骤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;
结论
这两种方法带来的结果完全相同。
如果组标识符的组中有两行具有最大值,则两种方法中的结果都是这两行。
这两种方法都与SQLANSI兼容,因此,无论其“味道”如何,都可以与您喜爱的RDBMS一起使用。
这两种方法都是性能友好的,但是您的里程可能会有所不同(RDBMS、DB结构、索引等)。确保你选择对你最有意义的那一个。
这个怎么样:
SELECT all_fields.*
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs
LEFT OUTER JOIN yourtable AS all_fields
ON max_recs.id = all_fields.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中的一个示例