SQL-左联接分组依据 [英] SQL - Group By with Left Join

问题描述

我有两个桌子.表A列出了员工姓名.表B是一个复杂的表，其中包含有关员工打来的电话的信息.

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.

我的目标是创建一个包含名称"和"callCount"列的表.我的目标是左加入"和分组依据"，但是我一直想念没有打过电话的员工.我如何才能保留名称并在其中放置零?

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?

也许我很近，有人可以指出我的错字吗?预先感谢您的帮助，这是SQL:

Perhaps I am close and someone can point out my typo? Thanks in advance for your help, here is the 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 

推荐答案

这是一个JOIN而非NULL问题:您的过滤器正在将OUTER更改为INNER JOIN.这意味着在CallTable(B)中只有行的地方获得COUNT，而不是您想要的OUTER JOIN.

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.

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 

在其他地方发表评论后，您对B的所有过滤器都必须在派生表中，而不是在外部位置.

after your comment elsewhere, all your filters on B must be in the derived table, not in the outer where.

这篇关于SQL-左联接分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！