过滤左连接

Question

如何正确过滤左连接中的数据?

我的任务是通过计算分配给每个支持单位的请求数量来创建支持单位当前负载的视图:

在我的MS SQL数据库中有两个表:

• Contact，包含支持单元
• Case，包含请求

如果我做一个简单的左连接，输出数据是OK的:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact 
left join [Case] on Contact.Id = [Case].OwnerId
Group By Contact.Id; 

输出:

<表类>与数tbody><<tr>id10Id22id30Id41

您需要将其添加到join语句中:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact 
left join [Case] on Contact.Id = [Case].OwnerId 
and [Case].StatusId = 'some id here'
Group By Contact.Id;

如果您想同时输出(count>0和count==0)。您可以添加或条件如下:

Select Contact.Id, count([Case].OwnerId) as [Count] from Contact
left join [Case] on Contact.Id = [Case].OwnerId
Where ([Case].StatusId = 'some id here' or [Case].id is null);