sql - 左外连接在 Postgres 中深入两层导致笛卡尔积

Question

给定以下 4 个表:

CREATE TABLE events ( id, name )
CREATE TABLE profiles ( id, event_id )
CREATE TABLE donations ( amount, profile_id )
CREATE TABLE event_members( id, event_id, user_id )

我正在尝试获取所有事件的列表，以及所有成员的数量和所有捐款的总和。问题是捐赠总和返回错误(似乎是捐赠 * # of event_members 的笛卡尔结果)。

这是 SQL 查询 (Postgres)

SELECT events.name, COUNT(DISTINCT event_members.id), SUM(donations.amount)
FROM            events
LEFT OUTER JOIN profiles      ON events.id = profiles.event_id
LEFT OUTER JOIN donations     ON donations.profile_id = profiles.id
LEFT OUTER JOIN event_members ON event_members.event_id = events.id
GROUP BY events.name

总和 (donations.amount) 返回 = 实际捐款总和 * event_members 中的行数。如果我注释掉 count(distinct event_members.id) 和 event_members left outer join，则总和是正确的。

Answer 1

正如我在对 the referenced question 的回答中所解释的那样您需要在 加入之前聚合以避免代理 CROSS JOIN。喜欢:

SELECT e.name, e.sum_donations, m.ct_members
FROM (
    SELECT e.id AS event_id, e.name, SUM(d.amount) AS sum_donations
    FROM   events         e
    LEFT   JOIN profiles  p ON p.event_id = e.id
    LEFT   JOIN donations d ON d.profile_id = p.id
    GROUP  BY 1, 2
    ) e
LEFT   JOIN (
    SELECT m.event_id, count(DISTINCT m.id) AS ct_members
    FROM   event_members m
    GROUP  BY 1
    ) m USING (event_id);

如果event_members.id是主键，那么id保证在表中是UNIQUE，可以drop DISTINCT 来自计数:

count(*) AS ct_members