SQL分类型统计员工三类信件登记量，解决关联计数虚高问题

此前查询结果出现单员工计数达数百万的量级偏差，本质是在未对多源信件表做预聚合的前提下直接做平级多表关联，触发了笛卡尔积效应。
具体来说，如果直接将employee表与incoming、outgoing、local三张原表做连续左连接，假设某员工在三类信件表中分别有n1、n2、n3条登记记录，关联后的总匹配行数会变为n1n2n3，原本几百条的登记记录会被乘积放大到百万级，最终统计的计数自然和实际存储量级完全不符。

核心思路是先聚合、后关联，从根源上避免笛卡尔积产生，共两种常用实现路径：

• 路径1：对三类信件表分别按登记员工ID独立聚合，得到每个员工在每类信件下的单行计数结果，再与员工表关联。因为每个员工在每个聚合结果集中最多对应1行，多表关联不会产生行数膨胀。
  对应的SQL实现为：

  WITH incoming_stat AS (
      SELECT 
          register_employee,
          COUNT(*) AS incoming_count
      FROM incoming
      GROUP BY register_employee
  ),
  outgoing_stat AS (
      SELECT 
          register_employee,
          COUNT(*) AS outgoing_count
      FROM outgoing
      GROUP BY register_employee
  ),
  local_stat AS (
      SELECT 
          register_employee,
          COUNT(*) AS local_count
      FROM local
      GROUP BY register_employee
  )
  SELECT 
      CONCAT(e.first_name, ' ', e.last_name) AS employee_full_name,
      COALESCE(i.incoming_count, 0) AS incoming_register_count,
      COALESCE(o.outgoing_count, 0) AS outgoing_register_count,
      COALESCE(l.local_count, 0) AS local_register_count
  FROM employee e
  LEFT JOIN incoming_stat i ON e.id = i.register_employee
  LEFT JOIN outgoing_stat o ON e.id = o.register_employee
  LEFT JOIN local_stat l ON e.id = l.register_employee
  

  其中COALESCE的作用是将未登记某类信件的员工对应计数值从NULL转为0，让输出结果更符合统计需求。
• 路径2：利用三类信件表结构完全一致的特点，先将三类信件的登记记录做全量合并、标记所属信件类型，再一次性关联员工表做分组统计。这种写法在表结构高度统一的场景下代码更简洁。
  对应的SQL实现为：

  SELECT 
      CONCAT(e.first_name, ' ', e.last_name) AS employee_full_name,
      COUNT(CASE WHEN letter_type = 'incoming' THEN 1 END) AS incoming_register_count,
      COUNT(CASE WHEN letter_type = 'outgoing' THEN 1 END) AS outgoing_register_count,
      COUNT(CASE WHEN letter_type = 'local' THEN 1 END) AS local_register_count
  FROM employee e
  LEFT JOIN (
      SELECT register_employee, 'incoming' AS letter_type FROM incoming
      UNION ALL
      SELECT register_employee, 'outgoing' AS letter_type FROM outgoing
      UNION ALL
      SELECT register_employee, 'local' AS letter_type FROM local
  ) all_letters ON e.id = all_letters.register_employee
  GROUP BY e.id, e.first_name, e.last_name
  

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