Oracle三表关联统计员工项目参与PJCount 无匹配记录记0

核心需求 #

• 统计EMPLOYEE表全量员工，符合以下规则的PJCount总和，结果返回员工UserAc及对应总计数：
  • 参与项目为Lakers
  • 活动类型为Client meeting（需匹配映射规则：Lakers项目下活动编码0200对应该类型）
  • 参与日期PDate落在2022/12/25-2022/12/27区间
• 无符合条件参与记录的员工（含从未出现在参与明细表的员工）也需返回，对应PJCount记为0
• 预期结果：Ken1对应9.6、John1对应0、Alex1对应3、Dan1对应0

涉及表结构 #

• EMPLOYEE：员工主表，存储员工账号字段UserAc，样例账号含Ken1、John1、Alex1、Dan1
• ProjectParticipate：项目参与明细表，字段包括PJName（项目名）、UserAc（参与人账号）、PJRn（活动编码）、PDate（参与日期）、PJCount（参与计数）
• ProjectGuide：项目活动编码映射表，字段包括PJName（项目名）、PJNumber（活动编码）、NumberExplan（活动类型名称），其中Lakers项目下编码0200对应Client meeting类型

初始SQL问题 #

原有单表查询逻辑存在两处硬伤，无法返回正确结果：

• 未关联ProjectGuide表做活动类型校验，直接硬编码PJRn='0200'，未绑定项目维度的编码映射规则，存在统计误差
• 未以员工主表为基表做左连接，无法返回无参与记录的员工，缺失空值补0逻辑

正确SQL代码 #

SELECT
    e.UserAc,
    NVL(SUM(p.PJCount), 0) AS total_PJCount
FROM EMPLOYEE e
LEFT JOIN ProjectParticipate p
    ON e.UserAc = p.UserAc
    AND p.PJName = 'Lakers'
    AND p.PDate BETWEEN TO_DATE('2022-12-25', 'yyyy-mm-dd') 
                    AND TO_DATE('2022-12-27', 'yyyy-mm-dd')
LEFT JOIN ProjectGuide g
    ON p.PJName = g.PJName
    AND p.PJRn = g.PJNumber
    AND g.NumberExplan = 'Client meeting'
GROUP BY e.UserAc
ORDER BY e.UserAc;

关键逻辑说明 #

• 以EMPLOYEE员工主表为左连接基表，从根源保证所有员工账号都会被返回，不会遗漏无记录人员
• 所有参与记录、活动映射的过滤条件全部写在LEFT JOIN的ON子句中，避免过滤条件写在WHERE子句导致左连接退化为内连接、丢失无记录员工的问题
• 用NVL()函数做空值处理：匹配不到符合条件的参与记录时，SUM返回的NULL值会被统一转换为0
• 关联编码映射表时同时匹配项目名、活动编码、活动类型名称，避免不同项目下编码重复导致的统计错误
• 日期条件使用TO_DATE()做显式类型转换，避免隐式转换导致的日期匹配失败、索引失效问题

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