MySQL如何将单用户未填工时日期查询扩展至所有用户

需求说明 #

用户需每日录入工时表条目，需要获取当月所有用户未录入工时表的日期列表，2022年11月的理想输出示例如下：

UserName | MissedDates
-----------------------
Joe       | 2022-11-20
Joe       | 2022-11-26
Joe       | 2022-11-27
Billy     | 2022-11-02
Billy     | 2022-11-09
etc       | etc
etc       | etc

当前实现问题 #

现有代码通过递归CTE生成当月所有日期，再用NOT EXISTS查询单个用户（user_id=83）的未录入日期，结果正确，但无法覆盖所有用户。现有代码如下：

SET @current_date:=CURDATE();
SET @StartDate:=date_add(date_add(LAST_DAY(@current_date),interval 1 DAY),interval -1 MONTH);
SET @EndDate:=LAST_DAY(@current_date);

CREATE TEMPORARY TABLE MonthlyDatesTbl
WITH RECURSIVE `MonthlyDates` AS
(
   SELECT @StartDate AS `day` 
   
   UNION ALL 
   
   SELECT date_add(`day`,interval 1 DAY) AS `day` FROM `MonthlyDates` WHERE `day` < @EndDate
)
SELECT * FROM `MonthlyDates`;


SELECT `MonthlyDatesTbl`.day FROM `MonthlyDatesTbl` 
WHERE NOT EXISTS(SELECT * FROM timesheet_entry WHERE timesheet_entry.user_id = 83 AND timesheet_entry.timesheet_entry_date = MonthlyDatesTbl.day);

DROP TEMPORARY TABLE MonthlyDatesTbl;

解决方案 #

核心思路是生成所有用户与当月所有日期的全量组合，再排除已存在工时记录的组合，剩下的就是每个用户未录入的日期。具体实现如下：

修改后的完整SQL代码 #

SET @current_date := CURDATE();
SET @StartDate := DATE_ADD(DATE_ADD(LAST_DAY(@current_date), INTERVAL 1 DAY), INTERVAL -1 MONTH);
SET @EndDate := LAST_DAY(@current_date);

WITH RECURSIVE MonthlyDates AS (
    SELECT @StartDate AS day
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY) 
    FROM MonthlyDates 
    WHERE day < @EndDate
),
AllUsers AS (
    -- 从用户表获取所有需要统计的用户，假设用户表名为users，包含user_id和username字段
    SELECT user_id, username 
    FROM users
)
SELECT 
    au.username AS UserName,
    md.day AS MissedDates
FROM AllUsers au
CROSS JOIN MonthlyDates md
WHERE NOT EXISTS (
    SELECT 1 
    FROM timesheet_entry te
    WHERE te.user_id = au.user_id 
      AND te.timesheet_entry_date = md.day
)
ORDER BY au.username, md.day;

关键说明 #

1. 递归CTE生成当月日期：保留原有的MonthlyDates逻辑，无需临时表，直接在CTE中生成。
2. 获取所有用户：通过AllUsers CTE从用户表（假设表名为users）取出所有用户的user_id和username，如果用户表结构不同，自行调整字段。
3. 交叉连接生成全量组合：用CROSS JOIN将每个用户与当月的每一天进行组合，得到所有可能的(user, date)对。
4. 筛选未录入记录：通过NOT EXISTS排除那些已经有工时条目的(user, date)组合，剩下的就是该用户未录入工时的日期。
5. 排序优化：最后按用户名和日期排序，让结果更易读。

注意事项 #

• 确保用户表（users）的表名和字段名与实际业务一致，若没有单独的用户表，也可以从timesheet_entry表中用DISTINCT user_id获取所有有过工时记录的用户，但这样会漏掉从未录过工时的用户。
• 如果需要排除周末或节假日，可在MonthlyDates CTE中添加筛选条件（比如DAYOFWEEK(day) NOT IN (1,7)排除周六周日）。

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