如何编写SQL查询生成任务日期区间内的单日期数组？

嘿，我来帮你搞定这个需求！你手里有个task表，存着任务的起止时间，想要把每个任务从开始到结束的所有日期（只保留日期部分）拆出来，生成指定格式的JSON数组对吧？

下面分两种主流数据库的情况给你提供查询语句：

适用于PostgreSQL的写法 #

PostgreSQL支持递归CTE和丰富的JSON函数，直接用下面的查询就能得到你要的结果：

WITH RECURSIVE date_range AS (
    -- 先把每个任务的起止时间转成纯日期格式，作为递归的起点
    SELECT 
        id,
        CAST(t_started_on AS DATE) AS allocatedDate,
        CAST(t_due_on AS DATE) AS end_date
    FROM task
    UNION ALL
    -- 递归生成每天的日期，直到达到任务的截止日期
    SELECT 
        id,
        allocatedDate + INTERVAL '1 day',
        end_date
    FROM date_range
    WHERE allocatedDate < end_date
)
-- 把所有日期转换成指定的JSON对象，再聚合为一个数组
SELECT JSON_AGG(JSON_BUILD_OBJECT('allocatedDate', allocatedDate)) AS result
FROM date_range
ORDER BY allocatedDate;

适用于MySQL 8.0+的写法 #

MySQL的递归CTE语法和JSON函数略有不同，调整后的查询如下：

WITH RECURSIVE date_range AS (
    SELECT 
        id,
        DATE(t_started_on) AS allocatedDate,
        DATE(t_due_on) AS end_date
    FROM task
    UNION ALL
    SELECT 
        id,
        DATE_ADD(allocatedDate, INTERVAL 1 DAY),
        end_date
    FROM date_range
    WHERE allocatedDate < end_date
)
SELECT JSON_ARRAYAGG(JSON_OBJECT('allocatedDate', allocatedDate)) AS result
FROM date_range
ORDER BY allocatedDate;

简单说下逻辑 #

1. 递归CTE的date_range部分：先提取每个任务的起始和截止日期（只保留日期部分），然后通过递归每天加1天，生成两个日期之间的所有日期。
2. 最后用JSON聚合函数，把每个日期转换成{"allocatedDate": "YYYY-MM-DD"}的格式，再拼成一个数组，就是你要的结果啦！

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