mysql - sql查询一个表中类别字段中max最大值对应的记录 - sql查询最大值




SQL只选择列上具有最大值的行 (20)

我有这张表格(这里是简体版):

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

我如何为每个ID选择一行而只选择最大的转速?
通过上述数据,结果应该包含两行: [1, 3, ...][2, 1, ..] 。 我正在使用MySQL

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

更新
正如答案所示,有一个SQL解决方案, 这里是一个sqlfiddle演示

更新2
我注意到在添加上面的sqlfiddle之后 ,问题被提高的速率已经超过了答案的满意率。 这并非意图! 小提琴基于答案,特别是被接受的答案。


乍一看...

所有你需要的是一个带有MAX聚合函数的GROUP BY子句:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

它从来没有这么简单,是吗?

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

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

实际上,社区已经创建了一个标签来处理像这样的问题: greatest-n-per-group 。

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

加入简单的group-identifier, max-value-in-group子查询

在这种方法中,您首先在子查询中找到group-identifier, max-value-in-group (上面已解决)。 然后,将您的表加入到子查询中,使用group-identifiermax-value-in-group上的等式:

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

左连接与自我,调整连接条件和过滤器

在这种方法中,您将自己与表连接起来。 平等,当然,在group-identifier 。 然后,2个聪明的动作:

  1. 第二个连接条件是左侧值小于右侧值
  2. 当您执行第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;

结论

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

如果您有两行group-identifiermax-value-in-group ,那么两个行都将在两种方法的结果中出现。

这两种方法都兼容SQL ANSI,因此,无论其“风味”如何,都可与您最喜爱的RDBMS一起使用。

两种方法都对性能友好,但是您的里程可能会有所不同(RDBMS,数据库结构,索引等)。 所以当你选择一种方法, 基准 。 并确保你选择对你最有意义的一个。


SELECT * FROM雇员,其中Employee.Salary(雇员组中雇员雇员通过Employe_id选择max(薪水))ORDER BY Employee.Salary


做这项工作的另一种方式是在OVER PARTITION子句中使用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 

在这篇文章中已经记录的另一个OVER PARTITION解决方案是

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上运行良好。


像这样?

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语句中有许多字段,并且您希望通过优化代码获得所有这些字段的最新值:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

如果有人正在寻找Linq verson,这似乎对我有用:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

我会用这个:

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


我几乎没有见过的第三种解决方案是具体的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限制。


我喜欢通过按列排列记录来完成此操作。 在这种情况下,按id分组排列rev值。 那些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两次。


我大吃一惊,没有答案提供SQL窗口功能解决方案:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

在SQL标准ANSI / ISO标准SQL:2003中添加并随后扩展了ANSI / ISO标准SQL:2008,窗口(或窗口)功能现在可供所有主要供应商使用。 有更多类型的排名功能可用于处理绑定问题: RANK, DENSE_RANK, PERSENT_RANK


我用下面的方法来解决我自己的问题。 我首先创建了一个临时表,并为每个唯一ID插入最大转速值。

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

然后,我将这些最大值(#temp1)加入了所有可能的id /内容组合。 通过这样做,我自然会过滤掉非最大的id /内容组合,并且剩下每个组合的最大转速值。

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

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

你可以使用IN做到这一点:

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

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


按照相反的顺序对rev字段进行排序,然后按照id进行分组,这样每个分组的第一行就是具有最高rev值的分组。

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

使用以下数据在http://sqlfiddle.com/进行测试

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

这在MySql 5.5和5.6中给出了以下结果

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

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

看起来有更简单的方法来做到这一点(但只在MySQL中 ):

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

这个问题上请给出用户波希米亚语的回答,以便为这个问题提供这样一个简洁和优雅的答案。

编辑:虽然这种解决方案适用于很多人,但长期来看可能并不稳定,因为MySQL不保证GROUP BY语句将返回不在GROUP BY列表中的列的有意义的值。 因此,使用此解决方案需要您自担风险


这个怎么样:

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

这些答案都不适合我。

这对我来说很有用。

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

这是一个很好的做法

使用以下代码:

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)

这是另一种解决方案,只有使用具有该字段最大值的字段才能检索记录。 这适用于我工作的平台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)

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);




greatest-n-per-group