SQL Joining by field that *可能*为空 [英] SQL Joining by field that *may* be empty

问题描述

在我的 SQL CE 数据库中，我有三个表:customer、list 和 customerlist(customer 之间的连接表code> 和 list - 因为它是多对多关系).

In my SQL CE database I have three tables: customer, list and customerlist (a junction table between customer and list - as it is a many-to-many relationship).

我正在尝试运行一个查询，该查询将显示所有当前列表以及当前订阅该列表的客户数量(从 customerlist 表中计算).

I am trying to run a query that will display all current lists with also the number of customers who are currently subscribed to that list (counting from customerlist table).

这是我当前的查询:

select list.listid, count(customerlist.customerid) as numppl, list.ShortDesc
from list inner join customerlist on list.listid=customerlist.listid
group by list.ShortDesc, list.listid
order by numppl desc

这个数据库的当前结构是:

The current structure of this database is:

[Customer]           [List]             [CustomerList]
CustomerId           ListId             CustomerListId
Name                 ShortDesc          CustomerId
Other details                           ListId

这当前返回所有当前分配有客户的列表 - 但不返回空列表.空列表被隐藏.

This currently returns all the lists who have customers currently assigned to them - but not lists which are empty. Empty lists are hidden.

我想修改此查询以也显示空列表，但我很挣扎.我想要的输出是:

I would like to modify this query to also display empty lists but I am struggling. My desired output is:

Name      numppl
listA     375
listB     45
listC     0

(在上面的例子中，listC 当前没有被返回).

(In the example above, listC is currently not being returned).

关于如何在查询中同时显示 listC 有什么想法吗?

Any thoughts on how to also show listC in the query?

推荐答案

使用 LEFT JOIN 代替 ISNULL 将 NULL 替换为 0:

Use LEFT JOIN instead with ISNULL to replace NULL with 0:

SELECT 
  list.listid, 
  ISNULL(count(customerlist.customerid), 0) AS numppl, 
  list.ShortDesc
FROM list 
LEFT JOIN customerlist ON list.listid = customerlist.listid
GROUP BY list.ShortDesc, 
         list.listid
ORDER BY numppl DESC;

SQL Fiddle 演示

对于 SQL Server CE，试试这个:

For SQL Server CE, try this:

SELECT 
  list.listid, 
  SUM(CASE WHEN customerlist.customerid IS NULL THEN 0 ELSE 1 END) AS numppl, 
  list.ShortDesc
FROM list 
LEFT JOIN customerlist ON list.listid = customerlist.listid
GROUP BY list.ShortDesc, 
         list.listid
ORDER BY numppl DESC;

这篇关于SQL Joining by field that *可能*为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！