我有一张文件表(此处为简化版):
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解决方案吗?
当前回答
这个怎么样:
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
其他回答
我想,你想要这个吗?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle:在此处检查
注意:在MySQL 8+天中,我可能不会再推荐这种方法了。好几年没用了。
第三种解决方案是MySQL特有的,看起来像这样:
SELECT id, MAX(rev) AS rev
, 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id
是的,它看起来很糟糕(转换为字符串和返回等),但根据我的经验,它通常比其他解决方案更快。也许这只是我的用例,但我已经在具有数百万条记录和许多唯一ID的表上使用了它。也许是因为MySQL在优化其他解决方案方面非常糟糕(至少在我提出这个解决方案的5.0天)。
一件重要的事情是GROUP_CONCAT对于它可以建立的字符串有一个最大长度。您可能希望通过设置group_concat_max_len变量来提高此限制。请记住,如果您有大量的行,这将是缩放的限制。
无论如何,如果您的内容字段已经是文本,则上述操作不会直接起作用。在这种情况下,您可能需要使用不同的分隔符,例如\0。您还将更快地达到group_concat_max_len限制。
此解决方案仅从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
当您将rev和id组合为MAX()的一个maxRevId值,然后将其拆分为原始值时,可以在不进行联接的情况下进行选择:
SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
FROM YourTable
GROUP BY id) x;
当存在复杂联接而不是单个表时,这一速度特别快。使用传统方法,复杂连接将进行两次。
当rev和id为INT UNSIGNED(32位)并且组合值适合BIGINT UNSIGNED时(64位),上述组合对于位函数很简单。当id和rev大于32位值或由多个列组成时,需要将该值合并为二进制值,例如,为MAX()添加适当的填充。
我会用这个:
select t.*
from test as t
join
(select max(rev) as rev
from test
group by id) as o
on o.rev = t.rev
子查询SELECT可能不太有效,但在JOIN子句中似乎是可用的。我不是优化查询的专家,但我在MySQL、PostgreSQL和FireBird上都做过尝试,而且效果非常好。
您可以在多个联接中使用此模式,也可以使用WHERE子句。这是我的工作示例(用表格“firmy”解决与您相同的问题):
select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
from firmy
group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'
这是在有十几张唱片的桌子上被要求的,在不太强的机器上只需要不到0.01秒的时间。
我不会使用IN子句(如上所述)。IN用于常量的短列表,而不是构建在子查询上的查询过滤器。这是因为in中的子查询是针对每个扫描的记录执行的,这可能会使查询花费很长时间。