SQL查询：匹配两表日期区间取ShiftAssignmentShiftID全为NULL如何解决

可能的问题原因 #

• 关联字段类型不匹配：tblshiftassignments.ShiftAssignmentEmployeeID 和 FirstColumns.EmployeeID 两个字段类型不一致（比如一个是带特殊字符的字符串、一个是数值型），导致关联条件失效
• 日期字段类型问题：tblshiftassignments 表的ShiftAssignmentStartDate、ShiftAssignmentEndDate是带时分秒的datetime类型，和selected_date的date类型比较时，时分秒部分导致范围判断不成立
• 日期范围逻辑冗余易出错：原写法ShiftAssignmentEndDate + INTERVAL 1 DAY > FirstColumns.selected_date 逻辑等价于ShiftAssignmentEndDate >= selected_date（仅针对纯日期类型），但如果字段是datetime类型会额外引入判断误差

修复后的SQL代码 #

SELECT 
    FirstColumns.selected_date,
    FirstColumns.WeekDay,
    FirstColumns.EmployeeName,
    FirstColumns.EmployeeID, 
    tblshiftassignments.ShiftAssignmentShiftID  
FROM (
    SELECT 
        EmployeeID,
        selected_date,
        CASE 
            WHEN WEEKDAY(selected_date) = 0 THEN 'MON' 
            WHEN WEEKDAY(selected_date) = 1 THEN 'TUES' 
            WHEN WEEKDAY(selected_date) = 2 THEN 'WED' 
            WHEN WEEKDAY(selected_date) = 3 THEN 'THU' 
            WHEN WEEKDAY(selected_date) = 4 THEN 'FRI' 
            WHEN WEEKDAY(selected_date) = 5 THEN 'SAT' 
            WHEN WEEKDAY(selected_date) = 6 THEN 'SUN' 
        END AS 'WeekDay', 
        tblemployee.EmployeeIDDisplay, 
        CONCAT(tblemployee.EmployeeLastName,',',tblemployee.EmployeeFirstName) AS 'EmployeeName' 
    FROM (
        SELECT adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
        FROM
            (SELECT 0 i union SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9) t0,
            (SELECT 0 i union SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9) t1,
            (SELECT 0 i union SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9) t2,
            (SELECT 0 i union SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9) t3,
            (SELECT 0 i union SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9) t4
    ) v,
    tblemployee 
    WHERE selected_date between '2021-08-01' and '2021-08-30'
    ORDER BY tblemployee.EmployeeLastName, selected_date
) AS FirstColumns 
LEFT JOIN tblshiftassignments 
ON TRIM(tblshiftassignments.ShiftAssignmentEmployeeID) = TRIM(FirstColumns.EmployeeID) 
AND DATE(tblshiftassignments.ShiftAssignmentStartDate) <= FirstColumns.selected_date 
AND DATE(tblshiftassignments.ShiftAssignmentEndDate) >= FirstColumns.selected_date
ORDER BY FirstColumns.EmployeeName,selected_date

额外验证建议 #

如果修改后还是匹配不到，可以先执行以下两个简单查询确认基础数据是否符合预期：

1. 单独执行FirstColumns子查询，确认输出的员工ID、日期范围符合预期
2. 单独查询tblshiftassignments表，确认存在对应员工ID、且日期范围覆盖2021年8月的有效记录

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