SQL - 左连接分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3231909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Group By with Left Join
提问by Brandi
I have two tables. Table A has a list of employee names. Table B is a complex table with information about phone calls made by employees.
我有两张桌子。表 A 有员工姓名列表。表 B 是一个复杂的表，其中包含有关员工拨打的电话的信息。
My goal is to make a table with columns 'name' and 'callCount'. I am aiming to do this with a 'left join' and a 'group by', but I keep missing the employees that have made no calls. How can I just get it to keep the name and just put a zero there?
我的目标是制作一个包含“name”和“callCount”列的表格。我的目标是通过“左加入”和“分组”来做到这一点，但我一直想念没有打电话的员工。我怎样才能让它保留名称并在那里放一个零？
Perhaps I am close and someone can point out my typo? Thanks in advance for your help, here is the SQL:
也许我很接近，有人可以指出我的错字？在此先感谢您的帮助，这里是 SQL：
SELECT A.name, COUNT(B.call_id) AS 'outgoing call count'
FROM EmployeeTable A
LEFT JOIN CallTable B
ON A.name = B.call_from_name
WHERE B.call_type LIKE 'outgoing'
AND B.voice_mail = '0'
...
GROUP BY A.name
回答by gbn
It's a JOIN not a NULL problem: your filter is changing the OUTER to an INNER JOIN. This means you only get COUNT where you have rows in CallTable (B) rather than the OUTER JOIN you wanted.
这是一个 JOIN 而不是 NULL 问题：您的过滤器正在将 OUTER 更改为 INNER JOIN。这意味着您只能在 CallTable (B) 中有行的地方获得 COUNT，而不是您想要的 OUTER JOIN。
SELECT A.name, COUNT(B.call_id) AS 'outgoing call count'
FROM
EmployeeTable A
LEFT JOIN
(
SELECT call_from_name, call_id FROM CallTable
WHERE call_type LIKE 'outgoing'
AND voice_mail = '0'
AND /* other CallTable filters */
) B
ON A.name = B.call_from_name
WHERE
/* only EmployeeTable A filters */
GROUP BY A.name
Edit: after your comment elsewhere, all your filters on B must be in the derived table, not in the outer where.
编辑：在您在其他地方发表评论之后，您对 B 的所有过滤器都必须在派生表中，而不是在外部 where 中。
回答by OMG Ponies
Because you're using a LEFT JOIN, references to the table defined in the LEFT JOIN can be null. The rows are there, you're just not seeing the count value as zero. Which means you need to convert this NULL value to zero (in this case):
因为您使用的是 LEFT JOIN，所以对 LEFT JOIN 中定义的表的引用可以为空。行在那里，您只是没有看到计数值为零。这意味着您需要将此 NULL 值转换为零（在这种情况下）：
SELECT A.name,
COALESCE(COUNT(B.call_id), 0) AS 'outgoing call count'
FROM EmployeeTable A
LEFT JOIN CallTable B ON B.call_from_name = A.name
AND B.call_type LIKE 'outgoing'
AND B.voice_mail = '0'
WHERE ...
GROUP BY A.name
This example uses COALESCE, an ANSI standard means of handling NULL values. It will return the first non-null value, but if none can be found it will return null. ISNULLis a valid alternative on SQL Server, but it is not portable to other databases while COALESCE is. Here's an MSDN article comparing the two functions.
此示例使用COALESCE，这是一种处理 NULL 值的 ANSI 标准方法。它将返回第一个非空值，但如果找不到，它将返回空值。 ISNULL是 SQL Server 上的有效替代方案，但它不能移植到其他数据库，而 COALESCE 是。这是一篇 MSDN 文章，比较了这两种功能。
