如何用SQL统计各员工在不同业务类别中的业务总量

嘿，这个需求我熟！要实现单查询获取每个员工在所有业务类别中的业务计数（包括0的情况），核心思路是从员工表出发，通过左连接关联各个业务类别表，再分组统计每个类别的数据量。这样就能确保哪怕员工在某个类别没有业务，也能返回0而不是被过滤掉。

最终SQL查询语句 #

SELECT 
    e.emp_bioid,
    e.emp_name,
    COUNT(dr.id) AS DoctorsCount,
    COUNT(lr.id) AS LawyersCount,
    COUNT(er.id) AS EducationsCount,
    COUNT(pr.id) AS ProfessionalsCount,
    COUNT(tr.id) AS TravelspaCount,
    COUNT(rest.id) AS RestaurantsCount,
    COUNT(lbr.id) AS LocalBusinessCount
FROM employees e
LEFT JOIN doctorsrating dr 
    ON e.emp_bioid = dr.emp_bioid 
    AND dr.countryid = $countryid 
    AND dr.createdAt BETWEEN '$fromDate' AND '$toDate'
LEFT JOIN lawyersrating lr 
    ON e.emp_bioid = lr.emp_bioid 
    AND lr.countryid = $countryid 
    AND lr.createdAt BETWEEN '$fromDate' AND '$toDate'
LEFT JOIN educationsrating er 
    ON e.emp_bioid = er.emp_bioid 
    AND er.countryid = $countryid 
    AND er.createdAt BETWEEN '$fromDate' AND '$toDate'
LEFT JOIN professionalsrating pr 
    ON e.emp_bioid = pr.emp_bioid 
    AND pr.countryid = $countryid 
    AND pr.createdAt BETWEEN '$fromDate' AND '$toDate'
LEFT JOIN travelsparating tr 
    ON e.emp_bioid = tr.emp_bioid 
    AND tr.countryid = $countryid 
    AND tr.createdAt BETWEEN '$fromDate' AND '$toDate'
LEFT JOIN restaurantsrating rest 
    ON e.emp_bioid = rest.emp_bioid 
    AND rest.countryid = $countryid 
    AND rest.createdAt BETWEEN '$fromDate' AND '$toDate'
LEFT JOIN localbusinessrating lbr 
    ON e.emp_bioid = lbr.emp_bioid 
    AND lbr.countryid = $countryid 
    AND lbr.createdAt BETWEEN '$fromDate' AND '$toDate'
GROUP BY e.emp_bioid, e.emp_name;

关键细节说明 #

• 左连接的使用：从employees表出发做左连接，保证所有员工都会出现在结果里，哪怕某个业务类别没有他们的数据，对应的计数会是0。
• 条件放在JOIN子句而非WHERE：如果把countryid和时间条件放在WHERE里，会把左连接后没有匹配的员工过滤掉（因为那些行的业务表字段都是NULL，WHERE条件会排除它们），所以必须把这些过滤条件放在每个JOIN的ON子句中，只过滤对应业务表的数据。
• COUNT(表.id)的作用：COUNT函数会忽略NULL值，所以如果员工在某个业务表没有匹配数据，COUNT(id)就会返回0，正好符合需求。
• 表名适配：我假设了各个业务类别的表名（比如律师是lawyersrating、教育是educationsrating），如果你的实际表名不一样，记得替换成对应的表名哦。

结果格式 #

执行这个查询后，返回的结果结构就和你期望的完全一致：

[
  { 
    "emp_bioid": "1234", 
    "emp_name": "XXXX", 
    "DoctorsCount": 3, 
    "LawyersCount": 0, 
    "EducationsCount": 6, 
    "ProfessionalsCount": 1, 
    "TravelspaCount": 0, 
    "RestaurantsCount": 0, 
    "LocalBusinessCount": 6 
  },
  ...
]

内容的提问来源于stack exchange，提问作者MahaOCTS