如何通过分组从多表查询并汇总员工年度假期天数

问题说明 #

现有两张业务表：

• Employee_Details（员工详情表）：存储员工基础信息，包含EmployeeId、EmployeeName、Department等字段
• Employees_Vacation（员工年度休假记录表）：存储员工休假记录，存在同一员工（相同EmployeeId）同年有多条假期天数记录的情况，需将同一员工同年的假期天数汇总为单条记录

原SQL查询未得到预期结果：

SELECT Employee_Details.EmployeeId, Employee_Details.EmployeeName, Employees_Vacation.Year, Employees_Vacation.Vacation, Employee_Details.Department
FROM Employees_Vacation INNER JOIN Employee_Details ON Employees_Vacation.EmployeeId=Employee_Details.EmployeeId group by Employee_Details.EmployeeId ORDER BY Employee_Details.EmployeeName, Employees_Vacation.Year ;

原查询错误原因 #

1. 仅按EmployeeId分组，未按Year分组，无法区分同一员工不同年份的休假记录
2. 直接选择Employees_Vacation.Vacation字段，未使用聚合函数对假期天数进行求和

正确SQL查询 #

SELECT 
    ed.EmployeeId,
    ed.EmployeeName,
    ev.Year,
    SUM(ev.Vacation) AS TotalVacationDays,
    ed.Department
FROM 
    Employees_Vacation ev
INNER JOIN 
    Employee_Details ed ON ev.EmployeeId = ed.EmployeeId
GROUP BY 
    ed.EmployeeId, ed.EmployeeName, ev.Year, ed.Department
ORDER BY 
    ed.EmployeeName, ev.Year;

关键说明 #

• 使用SUM(ev.Vacation)对同一员工同年的所有休假天数求和，别名TotalVacationDays更直观清晰
• 分组字段需包含所有非聚合的查询字段（EmployeeId、EmployeeName、Year、Department），符合SQL标准语法要求
• 使用表别名（ed、ev）简化SQL语句，提升代码可读性

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