试图计算2个表中的3列在我的组织表上的出现次数?我需要将这些事件连接到一个表中

Question

-- 2. In one table, show how many private topics, admins, and standard users each organization has.

SELECT organizations.name, COUNT(topics.privacy) AS private_topic, COUNT(users.type) AS user_admin, COUNT(users.type) AS user_standard
FROM  organizations
LEFT JOIN topics 
ON organizations.id=topics.org_id
AND topics.privacy='private'
LEFT JOIN users
ON users.org_id=organizations.id
AND users.type='admin'
LEFT JOIN users
ON users.org_id=organizations.id
AND users.type='standard'
GROUP BY organizations.name
;

org_id是user表和topics表的外键。它总是给我错误的结果，只计算管理员或标准用户的数量，并将其放在每列的所有行中。任何帮助都是非常感激的，因为我已经被困在这一段时间了!

所以，当我像你说的那样做时，我得到一个错误，这是用户表不能被指定不止一次。我按照你说的修改了代码，但还是没有。他们也没有给我任何样本数据，但我只是做了一些查询，看到了私有主题的次数，例如，这是在主题表的隐私列中。当我没有得到这个错误，我说，连接似乎覆盖自己，其中每一行的所有列是相同的最后一个连接。

Answer 1

在我看来，主题和用户没有关系。您只是试图在单个查询中获得结果。还有其他可能更好的方法来实现这一点，但我认为这将修复您已经得到的(假设每个表都有id列)

SELECT 
organizations.name,
COUNT(DISTINCT topics.id) AS private_topic,
COUNT(DISTINCT users.id) FILTER (WHERE users.type = 'admin')    AS user_admin, 
COUNT(DISTINCT users.id) FILTER (WHERE users.type = 'standard') AS user_standard`
FROM organizations
LEFT JOIN topics 
ON organizations.id = topics.org_id AND topics.privacy = 'private'
LEFT JOIN users
ON users.org_id = organizations.id
GROUP BY organizations.name;

我建议用一种更直接的方式:

SELECT 
min(o.name) as "name",
(
select count(*) from topics t
where t.org_id = o.id AND t.privacy = 'private'
) as private_topics,
(
select count(*) from users u
where u.org_id = o.id and u.type = 'admin'
) AS user_admin,
(
select count(*) from users u
where u.org_id = o.id and u.type = 'standard'
) AS user_standard
FROM organizations o
GROUP BY o.id;