图像这个：

select one_id,count(id) as num_Twos
from Two
group by one_id

输出：

one_id      num_Twos
1           2
2           1

然后加入主表（这样，表一中的主记录不重复，也不需要 distinct ）：

select
    id, num_Twos, isnull(num_Twos,0) as numTwos
from One 
    left join
    (
        select one_id,count(id) as num_Twos
        from Two
        group by one_id
    )as two
        ON id =two.one_id

输出：（使用 isnull - 放置 0 而不是 NULL）

id          num_Twos    numTwos
1           2           2
2           1           1
3           NULL        0
4           NULL        0

同样的事情，与表 3
来自评论：这样，您不需要顶级 group by ，因为所有信息已经汇总在() as Twoand() as Three

或不同的：

select one.id
        , count(distinct two.id) as num_twos
        , count(distinct three.id) as num_threes 
from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id

输出 ：

id          num_twos    num_threes
1           2           1
2           1           4
3           0           0
4           0           0

来源：

with One 
as (select 1 id union all select 2 union all select 3 union all select 4)
,Two 
as (select 1 as id, 1 as one_id Union all select 2,1 union all select 3,2)
,Three
as (select 1 as id,1 as one_id union all select 2,2 union all select 3,2 union all select 4,2 union all select 5,2)

此外，您可以将其重写为：

select id
        , isnull(num_twos,0) as num_twos
        , isnull(num_threes,0) as num_threes
from one
left join (select one_id ,count(id) as num_twos from two group by one_id) as two on one.id = two.one_id
left join (select one_id ,count(id) as num_threes from three group by one_id) as three on one.id = three.one_id