sql - 复杂的 Join Query，Join 3 tables with multiple group bys

Question

我有 3 个表:

推文:

CREATE TABLE tweets (
    text_content    VARCHAR(280) not null,
    username        VARCHAR(50) not null,
    timestamp       TIMESTAMP not null DEFAULT current_timestamp,
    id              UUID not null DEFAULT uuid_generate_v4(),
    CONSTRAINT      tweets_pk PRIMARY KEY (id)
);

喜欢:

CREATE TABLE likes (
    username        VARCHAR(50) not null,
    timestamp       TIMESTAMP not null default current_timestamp,
    post_id         UUID not null,
    CONSTRAINT      likes_pk PRIMARY KEY (username, post_id),
    CONSTRAINT      likes_post_id_fk FOREIGN KEY (post_id) REFERENCES tweets(id)
);

和转推

CREATE TABLE retweets (
    username        VARCHAR(50) not null,
    timestamp       TIMESTAMP not null default current_timestamp,
    post_id         UUID not null,
    CONSTRAINT      retweets_pk PRIMARY KEY (username, post_id),
    CONSTRAINT      retweets_post_id_fk FOREIGN KEY (post_id) REFERENCES tweets(id)
);

我需要一个查询，它会选择所有推文，以及它们的点赞和转发量。
我确实设法编写了一个有效的查询，但我认为我过于复杂了，希望听到更简单的解决方案!

Answer 1

您想在加入之前聚合。假设 join 键是 post_id:

select t.*, l.likes, r.retweets
from tweets t left join
     (select post_id, count(*) as likes
      from likes
      group by post_id
     ) l
     on l.post_id = t.id left join
     (select post_id, count(*) as retweets
      from retweets
      group by post_id
     ) r
     on r.post_id = t.id;