SQL给定日期区间如何重复返回TableA全量记录并关联TableB数据

问题原因 #

你之前的写法仅做了TableA和TableB的左关联，TableB中不存在的日期（比如示例中的28-08-2021）不会自动生成对应行，自然无法得到每个日期对应全量TableA记录的结果。

实现思路 #

1. 先生成起止日期范围内的所有连续日期序列
2. 将日期序列和TableA做笛卡尔积（交叉连接），得到每个日期对应全量TableA记录的基础数据集
3. 再将基础数据集和TableB做左关联，匹配对应日期、对应FID的记录即可

示例代码（以MySQL 8.0+为例） #

WITH RECURSIVE DateRange AS (
    -- 递归CTE生成连续日期，替换下方起止日期为你的实际参数即可
    SELECT '28-08-2021' AS QueryDate
    UNION ALL
    SELECT DATE_ADD(QueryDate, INTERVAL 1 DAY)
    FROM DateRange
    WHERE QueryDate < '30-08-2021'
)
SELECT 
    a.Id, 
    a.Name, 
    b.MyDate
FROM DateRange d
-- 交叉连接得到每个日期对应所有TableA的记录
CROSS JOIN TableA a
-- 左关联匹配TableB的对应记录
LEFT JOIN TableB b 
    ON b.FID = a.Id 
    AND b.MyDate = d.QueryDate
ORDER BY d.QueryDate, a.Id;

注意：请根据你的数据库实际日期存储格式调整起止日期的传参格式，避免日期匹配失败。如果使用的是其他数据库，可以替换日期序列的生成逻辑：

• Oracle 可使用CONNECT BY语法生成日期序列
• PostgreSQL 可直接用generate_series函数生成日期序列
• 低版本不支持CTE的MySQL，可以预先创建一张包含足够多连续日期的日历表，替代上述DateRange递归CTE使用。

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