如何统计并汇总两个及以上SQL独立表中各技术人员的出现次数

解法说明 #

先分别统计两张表中各人员的关联记录数，再关联两组统计结果计算总次数即可，通用SQL写法如下：

SELECT 
  COALESCE(r.technician, t.owner) AS `#Name`,
  COALESCE(r.request_cnt, 0) AS `#RequestIDcount`,
  COALESCE(t.task_cnt, 0) AS `#TaskIDcount`,
  COALESCE(r.request_cnt, 0) + COALESCE(t.task_cnt, 0) AS `#TotalCount`
FROM (
  -- 统计每人的请求数量
  SELECT technician, COUNT(#RequestID) AS request_cnt 
  FROM `Request Table`
  GROUP BY technician
) r
FULL OUTER JOIN (
  -- 统计每人的任务数量
  SELECT #owner AS owner, COUNT(#taskID) AS task_cnt 
  FROM `Task Table`
  GROUP BY #owner
) t ON r.technician = t.owner
ORDER BY #TotalCount DESC;

如果使用的是不支持FULL OUTER JOIN的数据库（如MySQL），可使用以下兼容写法：

SELECT 
  name AS `#Name`,
  SUM(request_cnt) AS `#RequestIDcount`,
  SUM(task_cnt) AS `#TaskIDcount`,
  SUM(request_cnt + task_cnt) AS `#TotalCount`
FROM (
  SELECT technician AS name, COUNT(#RequestID) AS request_cnt, 0 AS task_cnt 
  FROM `Request Table`
  GROUP BY technician
  UNION ALL
  SELECT #owner AS name, 0 AS request_cnt, COUNT(#taskID) AS task_cnt 
  FROM `Task Table`
  GROUP BY #owner
) AS total
GROUP BY name
ORDER BY #TotalCount DESC;

两种写法均可得到你给出的示例汇总结果，若字段名含特殊字符，可根据所用数据库添加对应转义符即可。

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