如何解决SQL查询中重复日期行与validWorkDay值矛盾问题

问题概况 #

• 需求实现：
  1. 递归生成holidays表中每条记录的开始日期到结束日期之间的所有节假日日期
  2. 判断tickets表中的日期是否为有效工作日：非周末且非节假日，是则返回1，否则返回0
• 现存问题：查询结果出现同一日期对应validWorkDay值相反的重复行，且无法创建新日历表

原代码问题分析 #

1. 递归CTE逻辑错误：feriados的WHERE子查询未关联当前递归行的entities_id，会匹配其他实体的节假日，导致递归生成的日期范围混乱；且子查询可能返回多条结果，破坏递归终止条件
2. 多对多关联导致重复：data_distinta中直接JOIN feriados，当工单日期对应多条节假日递归记录时，会生成同一日期的多条行，且CASE语句会因关联情况不同返回不同的validWorkDay值
3. 日期比较方式错误：用LIKE比较DATE类型的日期，属于字符串匹配逻辑，容易出现意外错误，应该用=直接比较
4. 冗余表关联：calendars、calendars_holidays、holidays在已有feriados CTE的情况下属于多余关联，会增加笛卡尔积，加剧重复行问题

修复方案 #

1. 修复递归CTE feriados：关联当前行的entities_id，确保递归仅针对当前节假日记录的日期区间
2. 用EXISTS子查询判断节假日：避免直接JOIN带来的多对多关联，仅判断日期是否存在于节假日列表中
3. 调整CASE逻辑顺序：先判断是否为节假日，再判断是否为周末，逻辑更清晰且避免冲突
4. 移除冗余表关联：减少不必要的关联，降低重复行概率
5. 对日期分组：确保每个日期仅返回一条记录，取正确的validWorkDay值

修复后的代码 #

WITH RECURSIVE feriados AS (
    SELECT begin_date AS dateValue, entities_id AS id
    FROM holidays
    UNION ALL
    SELECT DATE_ADD(dateValue, INTERVAL 1 DAY), id
    FROM feriados
    -- 关联当前行的id，确保只递归当前节假日的区间
    WHERE DATE_ADD(dateValue, INTERVAL 1 DAY) <= (SELECT end_date FROM holidays WHERE id = feriados.id)
),
data_distinta AS (
    SELECT 
        DATE(tickets.date) AS data,
        -- 先判断是否为节假日，再判断是否为周末
        CASE
            WHEN EXISTS (SELECT 1 FROM feriados WHERE feriados.dateValue = DATE(tickets.date) AND feriados.id = tickets.entities_id) THEN 0
            WHEN WEEKDAY(tickets.date) >= 5 THEN 0
            ELSE 1
        END AS validWorkDay
    FROM tickets
    -- 按日期分组，确保每个日期仅一条记录
    GROUP BY DATE(tickets.date)
)
SELECT data, validWorkDay
FROM data_distinta
-- 建议用YYYY-MM-DD格式避免日期歧义
WHERE data >= '2024-05-01'

额外说明 #

• 日期格式：原查询中的'05/01/2024'可能因数据库时区或格式设置出现歧义，统一使用YYYY-MM-DD格式更稳妥
• 实体关联：feriados中加入了entities_id的关联，确保判断节假日时匹配对应实体的节假日，避免跨实体的错误匹配

内容的提问来源于stack exchange，提问作者NIKOLAS CAVALHEIRO GONCALVES D