SQL计算员工总薪资时多津贴导致重复统计的处理方案

问题本质 #

现有SQL的缺陷根源是没有对一对多关联的津贴表、扣除表提前按员工做金额聚合，直接把主员工表和两个子表硬关联后，会产生多表关联的行数膨胀问题：

• 假如某员工有2条津贴记录、2条扣除记录，直接关联后会生成4条重复的员工行，每一行都会重复计算一次单条津贴、扣除的金额，最终导致总薪资虚高、结果行数不符合预期。
• 原SQL的GROUP BY把单条的津贴金额、扣除金额当成分组维度，根本没实现按员工汇总金额的目的，解决不了重复行的问题。

实现思路 #

核心就是先把两个一对多的子表，按员工ID提前聚合好每个员工的总津贴、总扣除金额，让每个员工在子表里只对应一条汇总记录，再和员工表、部门表关联，从根上避免关联后行数变多的问题，最终保证每个员工只返回一行结果。

通用实现代码：

SELECT 
    e.ename, 
    d.Dname,
    (e.Salary + ISNULL(allow.total_allow, 0) - ISNULL(ded.total_ded, 0)) AS total_salary,
    allow.total_allow AS allow,
    ded.total_ded AS ded
FROM Employee e
LEFT JOIN Dept d ON d.id = e.dept_id
-- 按员工聚合总津贴
LEFT JOIN (
    SELECT emp_id, SUM(amount) AS total_allow
    FROM allownces
    GROUP BY emp_id
) allow ON allow.emp_id = e.id
-- 按员工聚合总扣除
LEFT JOIN (
    SELECT emp_id, SUM(amount) AS total_ded
    FROM deduction
    GROUP BY emp_id
) ded ON ded.emp_id = e.id

实操注意点 #

• 两个聚合子查询必须只以emp_id为分组维度，确保每个员工在子查询里只返回一条汇总金额记录，彻底规避关联后的行数膨胀问题。
• 不要为了省事直接在外层对津贴、扣除金额做SUM聚合：因为两个一对多子表直接关联后会产生笛卡尔积，此时SUM的数值会被重复放大，统计结果完全失真，必须先分别聚合每个子表的员工维度总额，再做关联计算。
• 外层不需要额外加GROUP BY，因为所有关联的数据集都已经是员工维度的单行记录，直接查询就能得到每个员工的最终薪资结果。
• 要是用的数据库支持CTE语法，可以把两个聚合逻辑抽成公共表表达式，代码读起来更清楚，计算逻辑和上面的子查询写法完全一致。

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