在加入SQL Server时无法获得所需的输出

Question

我有3个表:

3. GroupCompanies

这是Certificate表:

+---+--------+------------+
| id| name   | company_id |
+---+--------+------------+
| 1 | cert01 | 1001       |
| 2 | cert02 | 1001       |
| 3 | cert03 | 1001       |
+---+--------+------------+

这是group_company表:

+----+---------+-------+------------+
| id | cert_id | gc_id | company_id |
+----+---------+-------+------------+
| 1  | 1       | 6     | 1001       |
| 2  | 2       | 6     | 1001       |
| 3  | 3       | 6     | 1001       |
+----+---------+-------+------------+

这是Role表

+----+---------+---------+------------+
| id | role_id | cert_id | company_id |
+----+---------+---------+------------+
| 1  | 1       | 1       | 1001       |
| 2  | 2       | 1       | 1001       |
| 3  | 1       | 2       | 1001       |
| 4  | 3       | 2       | 1001       |
| 5  | 3       | 3       | 1001       |
+----+---------+---------+------------+

现在cert_id为外键列，其引用列为certificate表的id。

下面是我得到的输出:

+----+--------+----------+------------+
| id | name   | gc_count | role_count |
+----+--------+----------+------------+
| 1  | cert01 | 2        | 2          |
| 2  | cert02 | 2        | 2          |
| 3  | cert03 | 1        | 1          |
+----+--------+----------+------------+

因此，role_count列在连接后是完美的，但gc_count显示不准确的数据，它应该显示计数为1。

这是小提琴和我到目前为止所尝试的:

小提琴联系

select distinct 
ct.id,
ct.name,
count(isnull(gc.cert_id)) as gc_count,
count(isnull(r.role_id)) as role_count 
from
certificate ct 
left join 
group_company gc on ct.id = gc.cert_id 
and ct.company_id = gc.company_id 
left join 
role r on r.cert_id = ct.id 
and r.company_id = ct.company_id
where 
ct.company_id = 1001
group by 
id, name;

Answer 1

在子查询中分别聚合从表，然后将它们连接到主表。

SELECT ct.*, gc.gc_count, r.role_count
FROM certificate ct
JOIN (
SELECT cert_id, company_id, COUNT(*) gc_count 
FROM group_company 
GROUP BY cert_id, company_id
) gc ON ct.id=gc.cert_id AND ct.company_id=gc.company_id
JOIN (
SELECT cert_id, company_id, COUNT(*) role_count 
FROM role
GROUP BY cert_id, company_id
) r ON r.cert_id=ct.id AND r.company_id=ct.company_id

https://dbfiddle.uk/LVWoeKUt

如果数据需要使用LEFT JOIN而不是INNER JOIN。