SQL查询需求：找出超出对应岗位休假额度的员工

首先先明确我们用到的数据表结构：

Employee24(EMPLOYEEID, FIRSTNAME, LASTNAME, GENDER, JOBROLES);
Leave25(EMPLOYEEID, LEAVEID, LEAVETYPE, STARTDATE, ENDDATE);
JOBROLESELIGIBLELE(JOBROLES, ELIGIBLE_SICK_LEAVES, ELIGIBLE_EARNED_LEAVES);

你的需求是找出超出对应岗位休假额度的员工，但当前的查询语句存在几个关键问题：

• 表名拼写错误：JOBROLESELIGIBELE应该是JOBROLESELIGIBLELE
• 日期计算逻辑错误：STARTDATE-ENDDATE得到的是负数，应该用ENDDATE - STARTDATE + 1来计算实际休假天数（比如从2024-01-01到2024-01-02是2天假期，直接相减会得到1，加1才是正确的天数）
• 子查询未关联员工岗位：没有将员工的JOBROLES和JOBROLESELIGIBLELE表关联，也没有按员工分组统计已休总天数，导致逻辑完全偏离需求

正确的SQL查询语句 #

SELECT e.*, total_leave_days, eligible_total
FROM Employee24 e
JOIN (
    -- 按员工分组，统计累计休假天数
    SELECT 
        EMPLOYEEID,
        SUM(ENDDATE - STARTDATE + 1) AS total_leave_days
    FROM Leave25
    GROUP BY EMPLOYEEID
) l ON e.EMPLOYEEID = l.EMPLOYEEID
JOIN (
    -- 计算每个岗位的总休假额度（病假+年假）
    SELECT 
        JOBROLES,
        ELIGIBLE_SICK_LEAVES + ELIGIBLE_EARNED_LEAVES AS eligible_total
    FROM JOBROLESELIGIBLELE
) j ON e.JOBROLES = j.JOBROLES
-- 筛选已休天数超过岗位额度的员工
WHERE l.total_leave_days > j.eligible_total;

逻辑拆解 #

1. 第一个子查询l：统计每个员工的累计休假天数，用ENDDATE - STARTDATE +1确保包含假期的首尾日期
2. 第二个子查询j：算出每个岗位的总休假额度，把病假和年假额度加总
3. 关联员工表、休假统计结果、岗位额度表，最后筛选出已休天数超过对应岗位额度的员工

如果需要区分病假和年假分别是否超假，可以用下面的查询：

SELECT e.*, sick_days, earned_days, j.ELIGIBLE_SICK_LEAVES, j.ELIGIBLE_EARNED_LEAVES
FROM Employee24 e
JOIN (
    SELECT 
        EMPLOYEEID,
        SUM(CASE WHEN LEAVETYPE = 'SICK' THEN ENDDATE - STARTDATE +1 ELSE 0 END) AS sick_days,
        SUM(CASE WHEN LEAVETYPE = 'EARNED' THEN ENDDATE - STARTDATE +1 ELSE 0 END) AS earned_days
    FROM Leave25
    GROUP BY EMPLOYEEID
) l ON e.EMPLOYEEID = l.EMPLOYEEID
JOIN JOBROLESELIGIBLELE j ON e.JOBROLES = j.JOBROLES
WHERE l.sick_days > j.ELIGIBLE_SICK_LEAVES 
   OR l.earned_days > j.ELIGIBLE_EARNED_LEAVES;

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