你可以在连接之前进行聚合，这样每个关系最多连接到一个（聚合）行，用于每组员工，活动和项目（这将防止你在使用多个匹配行的结果集的叉积时遇到重复计算的问题），然后在外部查询中，你可以SUM预聚合值以获得最终的总和：

SELECT MAX(c.NAME) AS company_name,
       COALESCE(SUM(e.employees), 0) AS employees,
       COALESCE(SUM(a.activities), 0) AS activities,
       COALESCE(SUM(p.projects), 0) AS projects
FROM   contact c
       INNER JOIN relation r
       ON c.ID = r.FID
       LEFT OUTER JOIN (
           SELECT id,
                  COUNT(id) AS employees
           FROM   contact
           WHERE  TYPE = 'PRS'
           GROUP BY id
       ) e
       ON (r.CID = e.ID)
       LEFT OUTER JOIN (
           SELECT id,
                  COUNT(id) AS activities
           FROM   activity
           GROUP BY id
       ) a
       ON (r.CID = a.ID)
       LEFT OUTER JOIN (
           SELECT id,
                  COUNT(id) AS projects
           FROM   project
           GROUP BY id
       ) p
       ON (r.FID = p.ID)
WHERE  c.TYPE = 'ORG'
GROUP BY c.ID;

字符串

• 注意：您也可以GROUP BY每个公司的唯一标识符，而不是名称，以防止当有两个公司具有相同名称时错误地聚合结果。

你可以使用下面的查询，如果它的工作。

SELECT 
    c.NAME AS company_name,
    COUNT(DISTINCT CASE WHEN c2.TYPE = 'PRS' THEN c2.ID END) AS employees,
    COUNT(DISTINCT CASE WHEN c3.ID IS NOT NULL THEN c3.ID END) AS activities,
    COUNT(DISTINCT CASE WHEN c4.ID IS NOT NULL THEN c4.ID END) AS projects
FROM contact c
LEFT JOIN relation r ON c.ID = r.FID
LEFT JOIN contact c2 ON r.CID = c2.ID AND c2.TYPE = 'PRS'
LEFT JOIN activity c3 ON r.CID = c3.ID
LEFT JOIN project c4 ON r.FID = c4.ID
WHERE c.TYPE = 'ORG'
GROUP BY c.NAME;

字符串