SQL Server多表连接后按BADGENUMBER汇总多月份薪资相关列

你需要为所有数值字段添加SUM聚合函数，同时增加GROUP BY子句按BADGENUMBER分组，即可实现每个工号仅返回一行汇总结果，修改后的查询语句如下：

SELECT
    dbo.USERINFO.BADGENUMBER ,
    SUM(dbo.Emp_OV_LT.Overtime) AS Total_Overtime ,
    SUM(dbo.Emp_OV_LT.Absent) AS Total_Absent , 
    SUM(dbo.Emp_OV_LT.Late + dbo.Emp_OV_LT.Early) AS Total_Late_Early , 
    ROUND(SUM(dbo.Salaries.B_S), 3) AS Total_B_S,
    ROUND(SUM(dbo.Salaries.T_A + dbo.Salaries.W_A + dbo.Salaries.L_A + dbo.Salaries.C_A), 3) AS Total_Allowance ,
    ROUND(SUM(dbo.Salaries.Overtime), 3) AS Total_Overtime_Salary,
    ROUND(SUM(dbo.Salaries.Absent), 3) AS Total_Absent_Deduction,
    ROUND(SUM(dbo.Salaries.Late_Deduction),3) AS Total_Late_Deduction , 
    ROUND(SUM(dbo.Salaries.Total_Salary),3) AS Sum_Total_Salary, 
    dbo.Salaries.SSN ,
    ROUND(SUM(dbo.Salaries.n_salary),3) AS Total_n_salary
FROM 
    dbo.USERINFO 
INNER JOIN 
    dbo.Salaries ON dbo.USERINFO.USERID = dbo.Salaries.User_ID 
INNER JOIN 
    dbo.Emp_Salary ON dbo.USERINFO.USERID = dbo.Emp_Salary.Emp_ID
INNER JOIN 
    dbo.Emp_OV_LT ON dbo.Emp_OV_LT.Emp_ID = dbo.USERINFO.USERID 
WHERE
    dbo.Salaries.Month = dbo.Emp_OV_LT.Month 
    AND dbo.Salaries.Month BETWEEN '1 - 2021' AND '8 - 2021' 
    AND dbo.Emp_OV_LT.year = '2021' 
    AND dbo.Salaries.ssn > 1 
GROUP BY
    dbo.USERINFO.BADGENUMBER,
    dbo.Salaries.SSN
ORDER BY
    dbo.Salaries.SSN,
    dbo.USERINFO.BADGENUMBER

核心修改说明 #

• 所有需要跨月累加的数值字段外层新增SUM()聚合函数，完成多月份数据汇总
• 新增GROUP BY子句，按BADGENUMBER（工号）分组，保证每个工号仅返回一行结果
• 调整ROUND()函数的执行顺序，先求和再保留三位小数，比单月四舍五入后再求和的精度更高。如果你的业务要求保留单月四舍五入的逻辑，可以将ROUND()放在SUM()内部
• 移除了原查询中按月份排序的规则，改为按社保号、工号排序

特殊场景适配 #

如果同一个BADGENUMBER下存在多条不同SSN的记录，可以将查询中的dbo.Salaries.SSN改为MAX(dbo.Salaries.SSN)，同时将SSN从GROUP BY子句中移除即可。

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