跨3个表的SQL计数

试着这样改变它:

SELECT  C.CategoryId 
    , C.CategoryName 
    , ISNULL(COUNT(CC.CharityId), 0) as CharityCount 
    , C.IsDeleted 
FROM    Charity.Categories C 
LEFT JOIN Charity.CharityCategories CC on C.CategoryId = CC.CategoryId 
LEFT JOIN Charity.Charities CH ON CC.CharityId = CH.CharityId 
    AND CH.IsApproved = 1 and CH.IsDeleted = 0 
GROUP BY C.CategoryId, C.CategoryName , C.IsDeleted 
Order By C.CategoryName 

通过在WHERE子句中引用CH(Charities),可以将其设置为当这些值为NULL(即Charities匹配中没有记录)时,其他表中的数据也被排除。

出于这个原因,我通常尽可能在join子句中包含所有约束/过滤器。