如何关联calendar与timesheet表，返回含未打卡时长（置0）的完整数据？

需求说明 #

现有calendar（日历表）和timesheet（打卡记录表）两张表：

• timesheet仅存储员工打卡日期的记录，未打卡日期无对应数据
• 需要关联两表，返回指定日期范围内、指定员工的所有日期记录，无打卡记录时将TimesheetHour显示为0

表结构与数据 #

calendar表 #

timesheet表 #

期望结果 #

返回指定日期范围（2023-09-07至2023-09-15）内指定员工（Matt Jr.、Jonas Ls）的全量记录，未打卡时TimesheetHour为0：

尝试过的SQL（未得到期望结果） #

WITH calendar AS 
(
    SELECT DISTINCT
        CalendarID,
        CalendarDate,
        Company,
        SUM((ENDTIME - STARTTIME) * EFFICIENCYPERCENTAGE / 100 / 3600) AS WorkingHours
    FROM 
        Calendar
    GROUP BY 
        CalendarDate, CalendarID, Company 
),
timesheet AS 
(
    SELECT DISTINCT
        Employee,
        CalendarDate,
        Company,
        CalendarID,
        TTimesheetHour
    FROM 
        Timesheet
)
SELECT
    cal.Company,
    cal.CalendarDate,
    cal.CalendarID,
    cal.WorkingHours,
    ts.Employee,
    COALESCE(ts.WorkingHours, 0) 'TimesheetHour'
FROM 
    calendar cal
FULL OUTER JOIN 
    timesheet ts ON ts.CalendarID = cal.CalendarID,
                 AND ts.CalendarDate = cal.CalendarDate
                 AND ts.Company = cal.Company
WHERE 
    cal.CalendarDate BETWEEN '2023-09-07' AND '2023-09-10'
    AND ((ts.Employee LIKE 'Matt Jr%') OR 
         ((ts.Employee LIKE 'Jonas Ls%'))

解决方案 #

问题分析 #

1. 原SQL使用FULL OUTER JOIN但通过WHERE条件过滤掉了未匹配的记录，无法保留未打卡的日期
2. 未构造指定员工与日历日期的全量组合，导致缺失员工未打卡的日期记录
3. 字段引用错误：timesheet CTE中字段名TTimesheetHour应为TimesheetHour，SELECT中错误引用了ts.WorkingHours

正确SQL #

WITH target_employees AS (
    -- 指定需要查询的员工及其对应公司、日历ID
    SELECT 'Matt Jr.' AS Employee, 'CompOne' AS Company, 'Sup-01' AS CalendarID
    UNION ALL
    SELECT 'Jonas Ls' AS Employee, 'CompOne' AS Company, 'Sup-02' AS CalendarID
),
calendar_range AS (
    -- 获取指定日期范围内的日历数据
    SELECT 
        Company,
        CalendarDate,
        CalendarID,
        WorkingHours
    FROM Calendar
    WHERE CalendarDate BETWEEN '2023-09-07' AND '2023-09-15'
)
SELECT
    cr.Company,
    cr.CalendarDate,
    cr.CalendarID,
    cr.WorkingHours,
    te.Employee,
    COALESCE(ts.TimesheetHour, 0) AS TimesheetHour
FROM calendar_range cr
-- 构造日历与目标员工的全量组合，确保每个员工对应所有日期
JOIN target_employees te 
    ON cr.Company = te.Company 
    AND cr.CalendarID = te.CalendarID
-- 左连接打卡表，保留所有日历+员工组合，未打卡时显示0
LEFT JOIN Timesheet ts 
    ON cr.Company = ts.Company
    AND cr.CalendarDate = ts.CalendarDate
    AND cr.CalendarID = ts.CalendarID
    AND te.Employee = ts.Employee
ORDER BY cr.CalendarDate, cr.CalendarID;

说明 #

1. target_employees CTE明确指定查询的员工及其关联的公司、日历ID，避免匹配错误的日历条目
2. calendar_range CTE筛选出目标日期范围内的所有日历数据
3. 通过JOIN生成日历与员工的全量组合，确保每个员工在指定日期内的每一天都有记录
4. LEFT JOIN关联打卡表，未匹配到打卡记录时用COALESCE将TimesheetHour设为0
5. 按日期和日历ID排序，结果与期望格式完全对齐

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