SQL仅选择列上具有最大值的行-Java 学习之路

949

我有这个文件表（这里是简化版）：

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

如何为每个id选择一行并且只选择最大的转速？
使用上述数据，结果应包含两行： [1, 3, ...] 和 [2, 1, ..] . 我正在使用 MySQL .

目前，我使用 while 循环中的检查来检测并覆盖结果集中的旧转速 . 但这是实现结果的唯一方法吗？是不是有 SQL 解决方案？

Update
正如答案所示，有一个SQL解决方案，和here a sqlfiddle demo .

Update 2
我注意到在添加上述sqlfiddle之后，问题被提升的速度超过了答案的upvote率 . 那不是故意的！小提琴是基于答案，特别是接受的答案 .

29 回答

这是另一个解决方案希望它能帮到某人

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

回复于 2024-04-23T18:10:46+08:00

5
将 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() 的适当填充的二进制值 .
回复于 2024-04-23T18:10:46+08:00
-5
我很惊讶没有答案提供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 .
回复于 2024-04-23T18:10:46+08:00

这是一个很好的方法

使用以下代码：

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)

回复于 2024-04-23T18:10:46+08:00

2

SELECT * FROM Employee里面的Employee.Salary（通过Employe_id从Employee组中选择max（salary））ORDER BY Employee.Salary

回复于 2024-04-23T18:10:46+08:00

我喜欢使用基于_98118的解决方案来解决这个问题：

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

回复于 2024-04-23T18:10:46+08:00

59
这是另一种仅使用具有该字段最大值的字段检索记录的解决方案 . 这适用于SQL400，这是我工作的平台 . 在此示例中，将通过以下SQL语句检索字段FIELD5中具有最大值的记录 .
```
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)
```
回复于 2024-04-23T18:10:46+08:00
1
我不能保证性能，但这是一个受Microsoft Excel限制的伎俩 . 它有一些很好的功能

GOOD STUFF
- 它应该强制只返回一个"max record"，即使有一个平局（有时是有用的）
- 不需要加入
APPROACH

它有点难看，需要您了解 rev 列的有效值范围 . 让我们假设我们知道 rev 列是介于0.00和999之间的数字，包括小数，但是小数点右边只有两位数（例如34.17是有效值） .

事情的要点是，您可以通过字符串连接/打包主要比较字段以及所需数据来创建单个合成列 . 通过这种方式，您可以强制SQL的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
```
打包开始时强制 rev 列为多个已知字符长度，而不管 rev 的值如何，例如
- 3.2变为1003.201
- 57变为1057.001
- 923.88成为1923.881
如果你做对了，两个数字的字符串比较应该产生与两个数字的数字比较相同的“max”，并且很容易使用substring函数转换回原始数字（它可以以一种形式或另一种形式提供）到处） .
回复于 2024-04-23T18:10:46+08:00

像这样的东西？

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)

回复于 2024-04-23T18:10:46+08:00

此解决方案只能从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

回复于 2024-04-23T18:10:46+08:00

5
我的偏好是使用尽可能少的代码......

你可以使用 IN 试试这个：
```
SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)
```
在我看来它不那么复杂......更容易阅读和维护 .
回复于 2024-04-23T18:10:46+08:00
39
乍一看......

你需要的只是一个带有 MAX 聚合函数的 GROUP BY 子句：
```
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
```
从来没有那么简单，是吗？

我刚刚注意到你也需要 content 列 .

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

实际上，StackOverflow社区创建一个标记只是为了处理这样的问题，这是很常见的：greatest-n-per-group .

基本上，您有两种方法可以解决该问题：

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

在这种方法中，您首先在子查询中找到 group-identifier, max-value-in-group （已经在上面解决过） . 然后，将表连接到 group-identifier 和 max-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
```
Left加入self，调整连接条件和过滤器

在这种方法中，你自己加入了表 . 当然，平等在 group-identifier . 然后，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;
```
结论

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

如果有两行 max-value-in-group 用于 group-identifier ，则两个行都将在结果中 .

这两种方法都是SQL ANSI兼容的，因此，无论其“风味”如何，它都可以与您喜欢的RDBMS一起使用 .

这两种方法都具有性能友好性，但您的里程可能会有所不同（RDBMS，DB结构，索引等） . 所以，当你选择一种方法而不是另一种方法时，基准 . 并确保你选择对你最有意义的那个 .
回复于 2024-04-23T18:10:46+08:00

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

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 / content组合中 . 通过这样做，我自然地过滤掉了非最大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

回复于 2024-04-23T18:10:46+08:00

我喜欢通过某些列对记录进行排名来做到这一点 . 在这种情况下，按 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 .

回复于 2024-04-23T18:10:46+08:00

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

回复于 2024-04-23T18:10:46+08:00

-1

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

回复于 2024-04-23T18:10:46+08:00

1
如果select语句中有许多字段，并且您希望通过优化代码获得所有这些字段的最新值：
```
select * from
(select * from table_name
order by id,rev desc) temp
group by id
```
回复于 2024-04-23T18:10:46+08:00
-2
另一种解决方案是使用相关子查询：
```
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的答案（子查询，leftjoin）中的解决方案的比较，基于MySQL测量，InnoDB表记录约1百万条记录，组大小为：1-3 .

对于全表扫描，子查询/左连接/相关时序彼此相关为6/8/9，当涉及直接查找或批处理（ id in (1,2,3) ）时，子查询比其他子查询慢得多（由于重新运行子查询） . 但是我无法区分leftjoin和相关解决方案的速度 .

最后一点，由于leftjoin在组中创建n *（n 1）/ 2个连接，其性能可能会受到组大小的严重影响......
回复于 2024-04-23T18:10:46+08:00
2
我会用这个：
```
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子句中使用此模式 . 这是我的工作示例（解决与你的问题相同的表“坚固”）：
```
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'
```
在有青少年的 table 上询问它和记录，并且在真正不太强的机器上花费少于0.01秒 .

我不会使用IN子句（因为它在上面的某处提到） . IN用于使用简短的constans列表，而不是在子查询上构建的查询过滤器 . 这是因为IN中的子查询是针对每个扫描记录执行的，这可以使查询花费很长时间 .
回复于 2024-04-23T18:10:46+08:00

以相反的顺序对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

回复于 2024-04-23T18:10:46+08:00

这些答案都没有对我有用 .

这对我有用 .

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

回复于 2024-04-23T18:10:46+08:00

1513

另一种方法是在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上运行良好 .

回复于 2024-04-23T18:10:46+08:00

0
NOT mySQL ，但对于其他人发现此问题并使用SQL，另一种解决greatest-n-per-group问题的方法是在MS SQL中使用Cross Apply
```
WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2
```
这是SqlFiddle中的一个例子
回复于 2024-04-23T18:10:46+08:00
41
我认为这是最简单的解决方案：
```
SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
```
- SELECT *：返回所有字段 .
- FROM员工：搜索表 .
- （SELECT * ...）子查询：返回所有人，按工资排序 .
- GROUPBY employeesub.Salary ::强制每个员工的排序最高的Salary行作为返回的结果 .
如果您碰巧只需要一行，那就更容易了：
```
SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1
```
我也认为最容易分解，理解和修改其他目的：
- ORDER BY Employee.Salary DESC：按薪水排序结果，首先是最高工资 .
- LIMIT 1：只返回一个结果 .
理解这种方法，解决任何这些类似问题变得微不足道：让员工获得最低工资（将DESC更改为ASC），获得前十名收入员工（将LIMIT 1更改为LIMIT 10），通过另一个领域进行排序（更改ORDER BY Employee.Salary to ORDER BY Employee.Commission）等 .
回复于 2024-04-23T18:10:46+08:00
1
我几乎没有看到的第三个解决方案是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 变量来提高此限制 . 请记住，如果您有大量行，这将是对缩放的限制 .

无论如何，上面的内容也不会更快地进入 group_concat_max_len 限制 .
回复于 2024-04-23T18:10:46+08:00
184
这适用于sqlite3：
```
SELECT *, MAX(rev) FROM t1 GROUP BY id
```
使用*，您会得到一个重复的rev列，但这不是什么大问题 .
回复于 2024-04-23T18:10:46+08:00

这个怎么样：

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

回复于 2024-04-23T18:10:46+08:00

如果有人正在寻找Linq版本，这似乎对我有用：

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) );
}

回复于 2024-04-23T18:10:46+08:00

0
由于这是关于这个问题的最受欢迎的问题，我将在这里重新发布另一个答案：

看起来有更简单的方法（但 only in MySQL ）：
```
select *
from (select * from mytable order by id, rev desc ) x
group by id
```
Please credit answer of user Bohemian 在this question中为这个问题提供了如此简洁优雅的答案 .

EDIT: though this solution works for many people it may not be stable in the long run, since MySQL doesn't guarantee that GROUP BY statement will return meaningful values for columns not in GROUP BY list. So use this solution at your own risk
回复于 2024-04-23T18:10:46+08:00

SQL仅选择列上具有最大值的行

29 回答

乍一看......

从来没有那么简单，是吗？

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

Left加入self，调整连接条件和过滤器

结论

这是SqlFiddle中的一个例子

相关问题