连接两个表并应用分组依据，但更改排序顺序 [英] Join two tables and apply group by, but change sort order

问题描述

我有两个表，事件和版本.一个事件有很多版本.我想执行内部联接并为每个事件获取最新版本.

I have two tables, event and version. An event has many versions. I want to perform an inner join and get THE LAST version for each event.

查询

SELECT * FROM events.event e INNER JOIN events.version v ON (e.version_id = v.id) GROUP BY e.event_id

餐桌活动

id  event_id    version_id  updated
1   1           7           03/08/2018
2   2           8           06/06/2018
3   2           9           02/07/2018

表版本

id      name            description         comments
7       Dinner          A fancy dinner Z    Comment Z
8       Breakfast       Fancy breakfast Y   Comment Y
9       Breakfast       Fancy breakfast X   Comment X

实际结果(在内部加入并分组之后)

ACTUAL RESULT (After inner join and group by)

id(e) event_id  version_id  id(v) name      description          comments
1     1         7           7     Dinner    A fancy dinner Z     Z
2     2         8           8     Breakfast Fancy breakfast Y    Y

期望结果

id(e) event_id  version_id  id(v) name      description          comments
1     1         7           7     Dinner    A fancy dinner Z     Z
2     2         9           9     Breakfast Fancy breakfast X    X

Order by对结果进行排序，是的，但是我需要的行没有放在第一位.我需要什么查询?谢谢！

Order by sorts the result yes, but the row I need is not there in the first place. What query do I need for this? Thanks!

推荐答案

在子查询中获取每个事件的最高版本.然后将表连接到子查询结果:

Get the max version per event in a subquery. Then join the tables to the subquery result:

SELECT e.*, v.*
FROM (
    SELECT event_id, MAX(version_id) as version_id
    FROM events.event
    GROUP BY event_id
) mx
JOIN events.event e USING(event_id, version_id)
JOIN events.version v ON v.id = e.version_id

这篇关于连接两个表并应用分组依据，但更改排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！