sql - 使用复杂的双重联接获取子对象的数量

注意，我正在使用postgresql
我有一个organizations表，一个users表，一个jobs表和一个documents表。我想得到一个组织的列表，按他们有权访问的文档总数排序。

organizations
------------
id (pk)
company_name

users
------------
id (pk)
organization_id

jobs
------------
id (pk)
client_id    (id of an organization)
server_id    (id of an organization)
creator_id   (id of a user)

documents
------------
id (pk)
job_id

期望的结果

organizations.id  |  organizations.company_name  |  document_count
85                |  Big Corporation             |  84
905               |  Some other folks            |  65
403               |  ACME, Inc                   |  14

如您所见，组织可以通过3种不同的路径连接到文档：
organizations.id=>jobs.client_id=>documents.job_id
organizations.id=>jobs.server_id=>documents.job_id
organizations.id=>users.organization_id=>jobs.creator_id=>documents.job_id
但是我想要一个查询，它将得到每个公司可以访问的所有文档的计数。。。
我试了几件事。。。这样地：

SELECT COUNT(documents.id) document_count, organizations.id, organizations.company_name
FROM organizations
INNER JOIN users ON organizations.id = users.organization_id
INNER JOIN jobs ON (
  jobs.client_id = organizations.id OR
  jobs.server_id = organizations.id OR
  jobs.creator_id = users.id
)
INNER JOIN documents ON documents.job_id = jobs.id
GROUP BY organizations.id, organizations.company_name
ORDER BY document_count DESC
LIMIT 10

查询需要一段时间才能运行，但这并不可怕，因为我是为一个一次性报告而执行的，但是结果。。。不可能是正确的。
第一个列出的组织报告的文档数为129834个，但这是不可能的，因为documents表中只有32820个记录。我觉得它一定是在计算大量的重复（由于我的一个连接出错？）但我不确定我错在哪里了。
顺序似乎是正确的，因为系统的最高音量用户显然在列表的顶部。。。但它的价值被夸大了。

最佳答案

问题是，如果jobs.client_id = organizations.id或jobs.server_id = organizations.id，那么就没有什么可以筛选您的INNER JOIN users（除了它的ON子句之外），因此您将为属于该组织的每个用户获得单独的记录。换句话说，对于每个组织，您要添加三个值：
它的用户总数乘以属于其客户端作业的文档总数
它的用户总数乘以属于其为服务器的作业的文档总数
属于其用户是创建者的作业的文档总数
解决此问题的一种方法是删除INNER JOIN users行，然后更改：

  jobs.creator_id = users.id

对此：

  jobs.creator_id IN (SELECT id FROM users WHERE organization_id = organizations.id)

. . . 但那可能表现得很糟糕。在找到可接受的查询之前，您可能需要尝试一些事情。

关于sql - 使用复杂的双重联接获取子对象的数量，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14844308/