我有三張桌子，一張，兩張和三張。

一

二

三

有了這個查詢

select one.id, count(two.id) as num_twos, count(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

我希望得到

1 2 1
2 1 4
3 0 0
4 0 0

因為有

• 二分之二的記錄與 one_id 1 相關，三分之二的記錄與 one_id 1 相關。
• 與 one_id 2 相關的二分之一記錄和與 one_id 2 相關的三分之四記錄。

但我明白了

為什麼？計數似乎成倍增加。我可以在沒有子查詢的情況下獲得所需的行為嗎？

圖像這個：

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

引用自：https://dba.stackexchange.com/questions/261573