选择具有两列分组的最大值的行 [英] Select rows with Max Value grouped by two columns

问题描述

关于这种问题，我已经看到了很多解决方案(尤其是这个

I have seen quite a few solutions on this kind of problem (esp. this one SQL Select only rows with Max Value on a Column), but none of these seem to be appropriate:

我具有以下表格布局，即绑定到实体的附件版本:

I have the following table layout, a versioning of attachments, which are bound to entities:

TABLE attachments
+------+--------------+----------+----------------+---------------+
| id   | entitiy_id   | group_id | version_number | filename      |
+------+--------------+----------+----------------+---------------+
| 1    | 1            | 1        | 1              | file1-1.pdf   |
| 2    | 1            | 1        | 2              | file1-2.pdf   |
| 3    | 1            | 2        | 1              | file2-1.pdf   |
| 4    | 2            | 1        | 1              | file1-1.pdf   |
| 5    | 2            | 1        | 2              | file1-2.pdf   |
| 6    | 2            | 3        | 1              | file3-1.pdf   |
+------+--------------+----------+----------------+---------------+

输出应为最大版本号，按group_id和Entity_id进行分组，如果有帮助，我只需要列出单个entity_id:

Output should be Max version number, grouped by group_id and entity_id, I'd only need a list for single entity_ids if that helps:

+------+--------------+----------+----------------+---------------+
| id   | entitiy_id   | group_id | version_number | filename      |
+------+--------------+----------+----------------+---------------+
| 2    | 1            | 1        | 2              | file1-2.pdf   |
| 3    | 1            | 2        | 1              | file2-1.pdf   |
| 5    | 2            | 1        | 2              | file1-2.pdf   |
| 6    | 2            | 3        | 1              | file3-1.pdf   |
+------+--------------+----------+----------------+---------------+

我想出的是这个自我加入者:

What I have come up with is this self join one:

SELECT *
FROM   `attachments` `attachments`
       LEFT OUTER JOIN attachments t2
         ON ( attachments.group_id = t2.group_id
              AND attachments.version_number < t2.version_number )
WHERE  ( t2.group_id IS NULL )
   AND ( `t2`.`id` = 1 )
GROUP  BY t2.group_id

但是，仅当不同的实体不共享相同的组号时，此选项才有效.不幸的是，这是必需的.

But this one only works if different entities do not share same group numbers. This, unfortunately is necessary.

在创建视图时遇到了一个可行的解决方案，但是当前设置不支持此操作.

I came across a working solution while creating a view, but this is not supported in my current setup.

任何想法都受到高度赞赏.谢谢！

Any ideas are highly appreciated. Thanks!

推荐答案

尝试一下:

select t1.* from attachments t1
left join attachments t2
on t1.entity_id = t2.entity_id and t1.group_id = t2.group_id and
   t1.version_number < t2.version_number
where t2.version_number is null

这篇关于选择具有两列分组的最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！