SQL 选择具有计数的其他列

Question

我们需要一个SQL查询来获取以下输出。

注意：表包含数百万记录。需要优化的查询

Select 
       E.EmpId, E.EmpName, E.Salary,
       (select count(*) from Dept where EmpId=E.EmpId) as DeptCount
from Employee E

Answer 1

尝试使用使用使用Count汇总。

Count aggregate function would not be counted when the value is NULL

 SELECT e.EmpId, e.EmpName, e.Salary,COUNT(d.EmpId) DeptCount
 FROM Employee e
 LEFT JOIN Dept d
 ON  d.EmpId = e.EmpId
 GROUP BY e.EmpId, e.EmpName, e.Salary

sqlfiddle