如何从MySQL逗号分隔字段中筛选当前周的工作日

看起来你的查询卡壳在两个关键问题上，咱们一步步拆解并修正：

核心问题点 #

1. 日期格式不匹配导致比较失效
   你的working_dates存储的是dd-mm-yyyy格式的字符串，而MySQL的CURRENT_DATE()返回的是yyyy-mm-dd格式。直接用字符串做BETWEEN比较时，会按字符顺序而非时间顺序判断，结果自然不对。
2. 缺少年份过滤+周范围计算逻辑偏差
   原查询只过滤了季度，没限制年份，容易拉取到同季度不同年份的旧数据；同时周范围的计算逻辑也有偏差，导致范围判断错误。

修正后的查询方案 #

方案1：基于日期范围的精准匹配（周一至周日为一周） #

SELECT STR_TO_DATE(Value, '%d-%m-%Y') AS value
FROM (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.working_dates, ',', n.n), ',', -1) AS Value
    FROM planned_production t
    CROSS JOIN (
        SELECT a.N + b.N * 10 + 1 AS n
        FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        ORDER BY n
    ) n
    WHERE t.quarter = QUARTER(CURDATE())
      AND t.year = YEAR(CURDATE())  -- 新增年份过滤，避免跨年度同季度数据干扰
      AND n.n <= 1 + (LENGTH(t.working_dates) - LENGTH(REPLACE(t.working_dates, ',', '')))
) AS TEMP_TABLE
WHERE STR_TO_DATE(Value, '%d-%m-%Y') BETWEEN 
    CURRENT_DATE() - INTERVAL WEEKDAY(CURRENT_DATE()) DAY  -- 当前周的周一
    AND 
    CURRENT_DATE() + INTERVAL (6 - WEEKDAY(CURRENT_DATE())) DAY  -- 当前周的周日
ORDER BY value ASC;

方案2：基于周数的匹配（更简洁，适配跨年场景） #

如果你的业务中存在跨年周的情况，用WEEK()函数判断会更稳妥：

SELECT STR_TO_DATE(Value, '%d-%m-%Y') AS value
FROM (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.working_dates, ',', n.n), ',', -1) AS Value
    FROM planned_production t
    CROSS JOIN (
        SELECT a.N + b.N * 10 + 1 AS n
        FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        ORDER BY n
    ) n
    WHERE t.quarter = QUARTER(CURDATE())
      AND t.year = YEAR(CURDATE())
      AND n.n <= 1 + (LENGTH(t.working_dates) - LENGTH(REPLACE(t.working_dates, ',', '')))
) AS TEMP_TABLE
WHERE WEEK(STR_TO_DATE(Value, '%d-%m-%Y'), 1) = WEEK(CURDATE(), 1)  -- 参数1表示周一为一周起始
  AND YEAR(STR_TO_DATE(Value, '%d-%m-%Y')) = YEAR(CURDATE())
ORDER BY value ASC;

关键修正说明 #

• 日期类型转换：用STR_TO_DATE(Value, '%d-%m-%Y')把字符串日期转成MySQL可识别的日期格式，确保时间比较逻辑正确。
• 年份过滤：新增t.year = YEAR(CURDATE())，避免误选同季度不同年份的历史数据。
• 周范围校准：调整了周起始/结束日期的计算逻辑，确保覆盖完整的当前周（如果你的周起始是周日，可以自行调整WEEKDAY()的计算参数）。

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