SQL如何对表多次自连接以正确获取员工及家属福利金额

你当前的SQL存在两个核心错误：

1. 使用普通内连接做自关联时，要求员工必须同时存在本人、子女、配偶三类登记记录，缺任意一类的员工都会被直接过滤，无法出现在结果中。
2. 自连接仅匹配了employee ssn，没有对关联关系做唯一限定，当员工存在多条子女/配偶登记记录（比如多子女、历史未失效的配偶登记）时，会产生笛卡尔积，导致返回重复行、福利金额匹配错位。

不需要做三次自连接，直接用条件聚合就可以实现需求，性能更高，也不会出现重复行、金额错配的问题：

SELECT 
    [employee ssn],
    MAX(CASE WHEN relationship = 'Employee' THEN [last name] END) AS [last name],
    MAX(CASE WHEN relationship = 'Employee' THEN [first name] END) AS [first name],
    SUM(CASE WHEN relationship = 'Employee' THEN [benefit amount] ELSE 0 END) AS [ee benefit],
    SUM(CASE WHEN relationship = 'Child' THEN [benefit amount] ELSE 0 END) AS [child benefit],
    SUM(CASE WHEN relationship = 'Spouse' THEN [benefit amount] ELSE 0 END) AS [spouse benefit]
FROM allenrollments
GROUP BY [employee ssn]

• 按employee ssn分组，将同一个员工的所有登记记录聚合到同一行，从根源上避免自连接产生的笛卡尔积重复问题
• 通过CASE语句按relationship取值分流福利金额：员工本人的福利计入ee benefit，所有子女的福利汇总计入child benefit，配偶的福利汇总计入spouse benefit，就算存在多条同类型亲属记录也会自动累加，不会出现金额错位
• 姓名字段通过条件判断仅提取relationship = 'Employee'行的姓名值，不会误取亲属的姓名
• 如果需要无对应福利时显示空值而非0，去掉语句中对应的ELSE 0即可
• 如果业务上确定每个员工最多只有1条本人、1条配偶、1条子女登记记录，可以把聚合函数SUM替换为MAX，返回结果一致。

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