我有一张文件表(此处为简化版):
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 yourtable.id, rev, content
FROM yourtable
INNER JOIN (
SELECT id, max(rev) as maxrev
FROM yourtable
GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
其他回答
我会用这个:
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中的子查询是针对每个扫描的记录执行的,这可能会使查询花费很长时间。
像这样吗?
SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
SELECT id, max(rev) as maxrev
FROM yourtable
GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
另一种方法是在OVERPARTITION子句中使用MAX()分析函数
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev
本文中已经记录的另一个ROW_NUMBER()OVERPARTITION解决方案是
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable
) t
WHERE t.rank = 1
此2 SELECT在Oracle 10g上运行良好。
MAX()解决方案的运行速度肯定比ROW_NUMBER()方案快,因为MAX()复杂性为O(n),而ROW_NUMBER()复杂性最低为O(n.log(n)),其中n表示表中的记录数!
我喜欢通过按某一列对记录进行排名来做到这一点。在这种情况下,按id对rev值进行分组。rev值越高的排名越低。所以,最高转速的排名为1。
select id, rev, content
from
(select
@rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
id, rev, content,
@prevValue := id
from
(select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
(select @rowNum := 1 from DUAL) X,
(select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;
不确定引入变量是否会使整个过程变慢。但至少我没有两次查询YOURTABLE。
我想,你想要这个吗?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle:在此处检查