MySQL如何按国家代码查询排班表中的首尾工作日

实现思路 #

表中DAY字段存储的是Sunday~Saturday格式的星期文本，直接按文本排序不符合自然周顺序，因此需要先通过FIELD函数自定义星期的周序映射，过滤出所有工作日记录后按国家分组，取周序最小的工作日为周首个工作日、周序最大的为周最后一个工作日即可。

SQL代码 #

SELECT
    COUNTRY_CODE,
    SUBSTRING_INDEX(
        GROUP_CONCAT(DAY ORDER BY FIELD(DAY, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')),
        ',',
        1
    ) AS START_WORKING_DAY,
    SUBSTRING_INDEX(
        GROUP_CONCAT(DAY ORDER BY FIELD(DAY, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') DESC),
        ',',
        1
    ) AS END_WORKING_DAY
FROM schedule
WHERE IS_WORKING = 'yes'
GROUP BY COUNTRY_CODE;

配置说明 #

• 代码中FIELD函数的星期顺序默认以周日为一周起始日，若业务要求以周一为周起始，直接调整参数顺序为'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'即可。
• 针对IN、UAE、UK三国的常规排班规则，查询返回结果如下：
  • UAE（工作日周日至周四）：START_WORKING_DAY = Sunday，END_WORKING_DAY = Thursday
  • IN、UK（工作日周一至周五）：START_WORKING_DAY = Monday，END_WORKING_DAY = Friday
• 该写法无嵌套子查询，执行效率高，只要表中存储了各国完整的周度排班数据，就不会返回空值结果。

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