mysql - SQL 在一列中查询行数据和最大值

  显示原文与译文双语对照的内容

我有一个用于文档( 简化的版本)的表格:


+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 1 |.. . |
| 2 | 1 |.. . |
| 1 | 2 |.. . |
| 1 | 3 |.. . |
+------+-------+--------------------------------------+

如何为每个标识选择一行,只选择最大的?
在上面的数据中,结果应该包含两行: [1, 3,.. .][2, 1,.. ] 。我使用的是磅的MySQL

当前,我使用 while 循环中的检查来检测和over-write来自resultset的旧转速。 但是这是唯一实现结果的方法? 是否有英镑的SQL 解决方案?

更新
答案表明, sql解决方案,这里 sqlfiddle演示。

更新2
我注意到在添加了上sqlfiddle之后,问题的upvoted的速率已经超过了答案的upvote速率。 这并不是有意的 ! 小提琴基于答案,尤其是接受的答案。

时间:

乍一看。。

你所需要的是一个 GROUP BY 条款 MAX 聚合函数:


select id, max(rev)
from YourTable
group by id

不是那么简单,是吧?

我只是注意到你需要 content 列。

这是SQL中非常常见的问题: 在每个组标识符中查找具有某些最大值的行的整个数据。 我在职业生涯中听到了很多。 实际上,这是我在当前的工作面试技术中回答的问题。

实际上,StackOverflow社区已经创建了一个单独的标签来处理这样的问题:

基本上,你有两种方法来解决这个问题:

连接简单 group-identifier, max-value-in-group Sub-query

在这种方法中,你首先发现 group-identifier, max-value-in-group ( 已经在sub-query中解决) 。 然后将你的表加入到sub-query中,在 group-identifiermax-value-in-group 上都有相等:


select yt.id, yt.rev, yt.contents
from YourTable yt
inner join(
 select id, max(rev) rev
 from YourTable
 group by id
) ss on yt.id = ss.id and yt.rev = ss.rev

自联接,调整联接条件和过滤器一起加入

在这种方法中,你使用自己的LEFT JOIN 。 当然,等式在 group-identifier 中。 然后,2智能移动:

  1. 第二个联接条件的左边值小于右边值
  2. 1当你一步,实际上最大的行值将在右边( 是 LEFT JOIN,记住) NULL 。 然后,我们过滤合并结果,只显示右边的行。

因此,你最终将使用:


select yt1.*
from yourtable yt1
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.rev <yt2.rev)
where yt2.id is null;

结束语

两种方法都带来了完全相同的结果。

如果你有两行对 group-identifiermax-value-in-group, 两行将这两种方法的结果。

这两种方法都是 SQL ANSI兼容的,因此可以与你喜爱的RDBMS一起使用,而不管它的"口味"。

两种方法都是性能友好的,但是你的里程可能会有所不同。 所以当你选择一种方法时,基准 。 并确保你选择了最适合你的人。

我的首选是尽可能少使用代码。。

你可以使用 IN 来执行这里操作:


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

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

我不能保证性能,但这是一个诡计灵感来自microsoftexcel的局限性。 它有一些好特性

好的STUFF

  • 即使有一个平局,它也应该只强制返回一个"最大记录数"( 有时是有用的)
  • 它不需要加入

方法

有点难看,要求你了解的有效值范围牧师列。 让我们假设我们知道牧师列是一个数字之间 0.00和 999包括小数,但只会有两个数字的小数点右边(e。g。 34.17将是一个有效值) 。

要点是,你创建一个合成列字符串连接/包装主比较字段以及你想要的数据。 通过这种方式,你可以强制 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

包装首先迫使牧师列是一个已知的字符长度无关的价值牧师以便为例

  • 3.2 变为 1003.201
  • 57变为 1057.001
  • 923.88 变为 1923.881

如果你做得正确,字符串比较的两个数字应该产生相同的"最大"数值比较两个数的和很容易转换回到最初使用substring函数( 它可以用一种形式或者其他形式随处可用) 数量。

类似这样的东西?


SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
 SELECT id, max(rev) as maxrev FROM yourtable
 WHERE yourtable
 GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

另一个解决方案是使用相关子查询:


select yt.id, yt.rev, yt.contents
 from YourTable yt
 where rev = 
 (select max(rev) from YourTable st where yt.id=st.id)

在( id,rev ) 上具有索引几乎将子查询呈现为简单查找。。

以下是比较 @AdrianCarneiro's 回答( 子查询,leftjoin )的解决方案,基于mysql ~1million innodb表的测量记录,集团规模: 1 -3 。

而对于全表扫描 subquery/leftjoin/correlated 计时与对方 6/8/9, 时直接查找或批( id in (1,2,3) ), 子查询是然后其他人( 由于重新运行子查询) 慢得多。 然而,在速度上我不能区分leftjoin和相关的解决方案。

最后一点,因为leftjoin在组中创建 n* ( n+1 )/2 连接,它的性能会受到组大小的严重影响。。

由于这个问题是最常见的问题,我将在这里另外回答一个问题:

看起来有更简单的方法来完成这个( 但只在mysql ):


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

请信贷回答用户 Bohemian这个问题提供这样一个简洁和优雅的回答这个问题。

编辑:虽然这个解决方案工作对很多人来说可能不稳定的从长远来看,因为mysql不保证 GROUP BY 语句会返回有意义的值列不是 GROUP BY 列表。 因此,在你自己的风险评估时使用这个解决方案

我喜欢使用基于 NOT EXIST的解决方案:


SELECT id, rev
FROM YourTable t 
WHERE NOT EXISTS (
 SELECT * FROM YourTable t WHERE t.id = id AND rev> t.rev)

我将使用:


select t.*
from test as t
join
 (select max(rev) as rev
 from test
 group by id) as o
on o.rev = t.rev

子查询选择不太eficient可能,但在JOIN子句中似乎可用。 我不是优化查询的专家,但我在 MySQL,PostgreSQL,火鸟等方面都尝试过,它确实很好。

可以在多个联接和where子句clause中使用这里架构。 这是我的工作示例( 用表格解决与你的问题相同的问题"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'

要求在表有青少年thusands记录,然后需要少 0,01第二真的不太强大的机器。

我不会在子句( 就像上面提到的) 中使用。 用于使用constans的短列表,而不是作为子查询生成的查询筛选器。 这是因为对每个扫描记录执行子查询,使得查询占用的时间非常长。

这个怎么样:


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

...