SQL如何查询两个日期间的已记录与未记录日期全量数据

核心实现逻辑是先生成你指定时间区间内的完整连续日期序列，再和现有业务统计结果左连接，补全缺失日期的默认值。

步骤1：生成连续日期序列 #

你原有查询使用了DATEPART函数，大概率是SQL Server环境，对应生成语句如下：

DECLARE @StartDate DATE = '2021-01-08'
DECLARE @EndDate DATE = '2021-11-17'

;WITH AllDates AS (
    SELECT @StartDate AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM AllDates
    WHERE date_val < @EndDate
)
SELECT 
    DATEPART(YEAR, date_val) AS year,
    DATEPART(MONTH, date_val) AS month,
    DATEPART(DAY, date_val) AS day
FROM AllDates
OPTION (MAXRECURSION 0)

其他数据库对应生成方式：

• MySQL 8.0+ 可使用递归CTE实现，逻辑一致，将日期函数替换为DATE_ADD即可
• PostgreSQL 可直接调用generate_series函数快速生成连续日期

步骤2：关联业务统计结果 #

把原有统计逻辑作为子查询，和上面的连续日期序列左连接，没有匹配的字段自动补0和NULL即可。完整修改后的SQL如下：

DECLARE @StartDate DATE = '2021-01-08'
DECLARE @EndDate DATE = '2021-11-17'
DECLARE @StartDatetime DATETIME = '2021-01-08 10:18:13'
DECLARE @EndDatetime DATETIME = '2021-11-17 10:40:09'

-- 生成连续日期序列
;WITH AllDates AS (
    SELECT @StartDate AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM AllDates
    WHERE date_val < @EndDate
),
-- 原有业务统计逻辑作为子查询
BusinessStats AS (
    SELECT 
        COUNT(activity_detail.activity_type_config_id) As count,
        user_det.full_name AS name, 
        DATEPART(DAY, activity_detail.created_date) AS day,
        DATEPART(MONTH, activity_detail.created_date) AS month,
        DATEPART(YEAR, activity_detail.created_date) AS year
    FROM activity_detail activity_detail
    INNER JOIN activity_type_config activity_type_config 
        ON activity_detail.activity_type_config_id = activity_type_config.activity_type_config_id
    INNER JOIN activity_user user_det 
        ON user_det.activity_user_id = activity_detail.activity_user_id
    WHERE activity_detail.created_date BETWEEN @StartDatetime AND @EndDatetime
    GROUP BY 
        user_det.full_name,
        DATEPART(MONTH, activity_detail.created_date),
        DATEPART(DAY, activity_detail.created_date),
        DATEPART(YEAR, activity_detail.created_date)
)
-- 左连接补全缺失日期
SELECT 
    ISNULL(bs.count, 0) AS count,
    bs.name,
    ad.day,
    ad.month,
    ad.year
FROM AllDates ad
LEFT JOIN BusinessStats bs 
    ON ad.year = bs.year 
    AND ad.month = bs.month 
    AND ad.day = bs.day
ORDER BY ad.year ASC, ad.month ASC, ad.day ASC
OPTION (MAXRECURSION 0)

补充说明 #

• 原有语句里的隐式连接已替换为更规范的显式INNER JOIN写法，逻辑保持不变
• 递归CTE的MAXRECURSION 0是为了避免默认递归次数限制，可生成超过100天的日期序列
• 若使用其他数据库，将ISNULL(bs.count, 0)替换为对应空值判断函数即可：MySQL用IFNULL(bs.count, 0)，PostgreSQL用COALESCE(bs.count, 0)
• 若你的环境不支持CTE，也可以提前维护一张日期维度表存储全量日期，直接关联维度表即可实现相同效果

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