使用多重连接的 SQL 计数

Question

我有以下三个相互关联的表:

1. 主持人(有多个 session )
2. session (有多个进程)
3. 过程

表结构如下:

1. 主机表 - id, name
2. session 表 - id, host_id, name
3. 进程表 - id, session_id, name

我想要实现的是每台主机上 session 数和进程数的计数。为此，我尝试了以下查询，但输出是错误的。

select host.id, 
       count(sessions.id) as "session count", 
       count(process.id) as "process count"
from host as host
     left outer join sessions as sessions on host.id = sessions.host_id
     left outer join process as process on sessions.id = process.session_id
group by host.id;

这是 SQLFiddle到架构。

根据 fiddle 中的数据，输出应该是:

id | session count | process count 
----------------------------------
1  |     2         |   3
2  |     1         |   2
3  |     1         |   2
4  |     2         |   3

但我得到的是:

id | session count | process count 
----------------------------------
1  |     3         |   3
2  |     2         |   2
3  |     2         |   2
4  |     3         |   3

获得所需输出的正确查询是什么？

Answer 1

不同；

select host.id, 
       count(distinct sessions.id) as "session count", 
       count(distinct process.id) as "process count"
from host as host
     left outer join sessions as sessions on host.id = sessions.host_id
     left outer join process as process on sessions.id = process.session_id
group by host.id;