sql获取具有与特定值匹配的最新关联的记录

5jvtdoz2 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(378)

上下文

使用postgresql，我正在存储 articles 及其相关的 events . 以下是两个表的简单结构，以便于示例：
文章表

+----+-----------+-----------------------+
| ID |   title   |      description      |
+----+-----------+-----------------------+
|  1 | article 1 | article 1 description |
|  2 | article 2 | article 2 description |
|  3 | article 3 | article 3 description |
|  4 | article 4 | article 4 description |
|  5 | article 5 | article 5 description |
+----+-----------+-----------------------+

事件表

+-----+-----------+--------------+----------------+--------------+
| ID  |   name    | eventable_id | eventable_type | created_at   |
+-----+-----------+--------------+----------------+--------------+
|  1  | validated |            1 | Article        | 2020-05-10   |
|  2  | reported  |            1 | Article        | 2020-05-11   |
|  3  | reported  |            2 | Article        | 2020-05-10   |
|  4  | reported  |            2 | Article        | 2020-05-11   |
|  5  | reported  |            2 | Article        | 2020-05-12   |
|  6  | reported  |            3 | Article        | 2020-05-20   |
|  7  | validated |            3 | Article        | 2020-05-21   |
|  8  | reported  |            4 | Article        | 2020-05-12   |
|  9  | moved     |            4 | Article        | 2020-05-13   |
|  10 | reported  |            4 | Article        | 2020-05-14   |
|  11 | moved     |            5 | Article        | 2020-05-13   |
|  12 | moved     |            5 | Article        | 2020-05-14   |
+-----+-----------+--------------+----------------+--------------+

问题

在这里我需要能够得到所有 articles 最新的 events 存在 reported .
例如，根据上面的数据，我们只能得到： article 1 ：因为它已经 validated 那么
reported article 2 ：因为它只是
reported article 4 ：因为它已经 moved 那么 reported （同第一条）
如你所见： article 3 ：不应返回，因为其最新事件为 validated . article 5 ：不应返回，因为其最新事件为 moved .
我很容易找到所有的 articles 有一个 reported 事件。但是如何让那些有最新活动的人 reported ?
以下是我到目前为止所做的尝试，但没有成功：

SELECT *
FROM articles a
INNER JOIN (
    SELECT *
    FROM events
    WHERE name = 'reported'
    ORDER BY created_at
    LIMIT 1
) AS e ON e.moderable_id = a.id AND e.moderable_type = 'Article'

我们目前有：
459 892 articles 62 074 events

sql postgresql Join Date greatest-n-per-group

来源：https://stackoverflow.com/questions/62034703/sql-get-records-having-latest-association-matching-a-specific-value

1条答案

按热度按时间

4bbkushb1#

可以使用相关子查询进行筛选：

select a.*
from articles a
where 
    (
        select e.name
        from events e
        where e.eventable_id = a.id and e.eventable_type = 'Article'
        order by created_at desc 
        limit 1
    ) = 'reported'

我们也可以用横向连接来表示：

select a.*
from articles a
inner join lateral (
    select e.name 
    from events 
    where e.eventable_id = a.id and e.eventable_type = 'Article'
    order by created_at desc 
    limit 1
)  x on x.status = 'reported'

赞(0）回复(0）举报 2021-07-26

我来回答

sql获取具有与特定值匹配的最新关联的记录

上下文

问题

1条答案

相关问题

热门标签

最新问答