基于工作日标记的行复制及日期计算SQL查询实现

现有数据表结构如下：

ID; first_sunday_day; name; address; monday; tuesday; wednesday; thursday; friday; saturday; sunday
1; 20230812;test;myaddress;0;0;1;1;0;0;0

需求：将表中标记为1的工作日对应的行复制，同时根据first_sunday_day计算对应工作日的日期（示例中20230812为周日，周三加3天得20230815，周四加4天得20230816），最终得到结果：

Name;address;newdate;
test;myaddress;20230815
test;myaddress;20230816

请问如何通过查询语句实现该需求？

可以通过UNION ALL拆分每个工作日列，筛选出标记为1的记录，再计算对应日期。以下是主流数据库的实现方式：

MySQL #

SELECT 
    name,
    address,
    DATE_FORMAT(DATE_ADD(STR_TO_DATE(first_sunday_day, '%Y%m%d'), INTERVAL day_offset DAY), '%Y%m%d') AS newdate
FROM (
    SELECT name, address, first_sunday_day, 1 AS day_offset FROM your_table WHERE monday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 2 AS day_offset FROM your_table WHERE tuesday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 3 AS day_offset FROM your_table WHERE wednesday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 4 AS day_offset FROM your_table WHERE thursday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 5 AS day_offset FROM your_table WHERE friday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 6 AS day_offset FROM your_table WHERE saturday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 0 AS day_offset FROM your_table WHERE sunday = 1
) AS temp
ORDER BY newdate;

• STR_TO_DATE将数字格式的日期转换为日期类型
• DATE_ADD添加对应工作日的偏移量（周日偏移0，周一1，……，周六6）
• DATE_FORMAT将计算后的日期转回YYYYMMDD格式

PostgreSQL #

SELECT 
    name,
    address,
    TO_CHAR((TO_DATE(first_sunday_day::TEXT, 'YYYYMMDD') + day_offset), 'YYYYMMDD') AS newdate
FROM (
    SELECT name, address, first_sunday_day, 1 AS day_offset FROM your_table WHERE monday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 2 AS day_offset FROM your_table WHERE tuesday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 3 AS day_offset FROM your_table WHERE wednesday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 4 AS day_offset FROM your_table WHERE thursday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 5 AS day_offset FROM your_table WHERE friday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 6 AS day_offset FROM your_table WHERE saturday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 0 AS day_offset FROM your_table WHERE sunday = 1
) AS temp
ORDER BY newdate;

• 先将first_sunday_day转为文本，再用TO_DATE转换为日期类型
• 直接用+运算符添加天数偏移量
• TO_CHAR将日期格式化为目标格式

SQL Server #

SELECT 
    name,
    address,
    CONVERT(VARCHAR(8), DATEADD(DAY, day_offset, CONVERT(DATE, CAST(first_sunday_day AS VARCHAR(8)), 112)), 112) AS newdate
FROM (
    SELECT name, address, first_sunday_day, 1 AS day_offset FROM your_table WHERE monday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 2 AS day_offset FROM your_table WHERE tuesday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 3 AS day_offset FROM your_table WHERE wednesday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 4 AS day_offset FROM your_table WHERE thursday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 5 AS day_offset FROM your_table WHERE friday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 6 AS day_offset FROM your_table WHERE saturday = 1
    UNION ALL
    SELECT name, address, first_sunday_day, 0 AS day_offset FROM your_table WHERE sunday = 1
) AS temp
ORDER BY newdate;

• 先将数字转为字符串，再用CONVERT(DATE, ..., 112)转换为日期（112对应YYYYMMDD格式）
• DATEADD添加天数偏移量
• 最后用CONVERT转回YYYYMMDD格式的字符串

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