关联考勤与调班表查询月度员工调班数据出现重复日期，求正确SQL语句

问题根因 #

• LEFT JOIN后WHERE子句中使用了关联表transaksiChangeShift的非空字段作为筛选条件，实际上等效于INNER JOIN，无调班记录的考勤日期会被过滤
• 同一考勤日期可能匹配到多条时间范围重叠的调班记录，直接GROUP BY无法区分调班优先级，导致出现重复日期数据

修正后的查询语句 #

使用窗口函数给每个考勤日期匹配到的调班记录按优先级排序，取优先级最高的1条即可，排序规则匹配示例的业务逻辑：时间范围越小的调班优先级越高，同范围下后录入的调班优先级更高

SELECT 
    t.NIK,
    t.enroll,
    t.idShift
FROM (
    SELECT
        l.NIK,
        l.enroll,
        cs.idShift,
        -- 按调班时间范围长度升序、调班表主键倒序排列，优先级高的排第一位
        ROW_NUMBER() OVER (
            PARTITION BY l.enroll 
            ORDER BY DATEDIFF(IFNULL(cs.dateEnd, cs.dateStart), cs.dateStart) ASC, cs.idx DESC
        ) AS rn
    FROM attandenceLog l
    LEFT JOIN transaksiChangeShift cs 
        ON l.NIK = cs.NIK
        AND DATE(l.enroll) BETWEEN DATE(cs.dateStart) AND IFNULL(DATE(cs.dateEnd), DATE(cs.dateStart))
    WHERE l.NIK = '007' -- 若NIK为字符串类型需加引号，避免字段隐式转换
        -- 补充1个月数据筛选条件，可根据实际业务调整时间范围
        AND l.enroll >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
) t
WHERE t.rn = 1
ORDER BY t.enroll;

适配说明 #

• 如果业务规则为「后提交的调班完全覆盖之前的重叠时间段」，可直接将窗口函数的ORDER BY逻辑修改为cs.idx DESC，输出结果和你给出的示例完全一致
• 若数据库中日期存储为dd/mm/yyyy格式的字符串，需要用STR_TO_DATE函数统一转为日期类型再比较，比如STR_TO_DATE(cs.dateStart, '%d/%m/%Y')
• 原查询中写的idGroupShift和示例调班表的idShift字段名不一致，请根据实际表结构调整字段名

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