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

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

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

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


当前回答

由于这是关于这个问题最流行的问题,我将在这里重新发布另一个答案:

看起来有更简单的方法(但仅在MySQL中):

select *
from (select * from mytable order by id, rev desc ) x
group by id

请感谢用户Bohemian在这个问题上的回答,因为它为这个问题提供了如此简洁而优雅的答案。

编辑:虽然这个解决方案对很多人都有效,但从长远来看它可能并不稳定,因为MySQL不能保证GROUP BY语句会为不在GROUP BY列表中的列返回有意义的值。因此,使用此解决方案的风险自负!

其他回答

我想,你想要这个吗?

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)  

SQL Fiddle:在此处检查

SELECT *
FROM Employee
where Employee.Salary in (select max(salary) from Employee group by Employe_id)
ORDER BY Employee.Salary

我会用这个:

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中的子查询是针对每个扫描的记录执行的,这可能会使查询花费很长时间。

这是一个很好的方法

使用以下代码:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

唯一标识符?对唯一标识符!

开发MySQL数据库的最佳方法之一是使用每个id AUTOINCREMENT(SourceMySQL.com)。这有很多优点,这里无法介绍。这个问题的问题是它的示例有重复的id。这忽视了唯一标识符的这些巨大优势,同时也让熟悉这一点的人感到困惑。

最清洁的解决方案

数据库Fiddle

较新版本的MySQL默认情况下启用了ONLY_FUL_GROUP_BY,这里的许多解决方案在测试中都会失败。

即使如此,我们也可以简单地选择DISTINCT someuniquefield、MAX(要选择的任何其他字段)、(*somethirdfield)等,而不必担心会了解结果或查询的工作方式:

SELECT DISTINCT t1.id, MAX(t1.rev), MAX(t2.content)
FROM Table1 AS t1
JOIN Table1 AS t2 ON t2.id = t1.id AND t2.rev = (
    SELECT MAX(rev) FROM Table1 t3 WHERE t3.id = t1.id
)
GROUP BY t1.id;

SELECT DISTINCT Table1.id,max(Table1.rev),max(Table2.content):返回DISTINCT somefield,max()某个其他字段,最后一个max()是多余的,因为我知道它只是一行,但查询需要它。FROM雇员:在上搜索了表。JOIN Table1 AS Table2 ON Table2.rev=Table1.rev:在第一个表上加入第二个表,因为我们需要得到max(Table1.rev)的注释。GROUP BY Table1.id:强制将每个员工的排名靠前的薪资行作为返回结果。

请注意,由于OP的问题中“内容”是“…”,因此无法测试这是否有效。因此,我将其更改为“..a”,“..b”,因此,我们现在可以看到结果是正确的:

id  max(Table1.rev) max(Table2.content)
1   3   ..d
2   1   ..b

为什么它是干净的?DISTINCT()、MAX()等都很好地利用了MySQL索引。这会更快。或者,如果您有索引,并将其与查看所有行的查询进行比较,则速度会快得多。

原始解决方案

在ONLY_FUL_GROUP_BY被禁用的情况下,我们仍然可以使用GROUP BY,但是我们只在Salary上使用它,而不在id上使用它:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;

SELECT*:返回所有字段。FROM雇员:在上搜索了表。(SELECT*…)子查询:返回所有人员,按薪资排序。GROUP BY employeesub。Salary:强制将每个员工的顶部排序的Salary行作为返回结果。

唯一行解决方案

请注意关系数据库的定义:“表中的每一行都有自己的唯一键。”这意味着,在问题的示例中,id必须是唯一的,在这种情况下,我们可以这样做:

SELECT *
FROM Employee
WHERE Employee.id = 12345
ORDER BY Employee.Salary DESC
LIMIT 1

希望这是一个解决问题的解决方案,帮助每个人更好地了解数据库中发生的事情。