如何修改SQL查询，使无数据日期返回A、B列0值及对应时间戳

你的需求核心是把查询时间范围内的每一天都纳入结果集，哪怕当天没有符合Bad=1的记录。原查询只返回有数据的日期，是因为它直接从Log表过滤后分组，缺失的日期自然不会出现。下面是针对SQL Server（你的语法看起来是这个环境）的两种可行方案：

方案1：用递归CTE生成日期序列（无需额外表） #

这种方法适合临时需求，不需要提前创建日期表。我们先生成目标时间范围内的所有日期，再和原查询的聚合结果做左连接，最后用COALESCE把NULL转为0：

-- 第一步：生成时间范围内的所有日期
WITH DateRange AS (
    SELECT CAST('2018-03-01' AS DATE) AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRange
    WHERE DateValue < CAST('2018-04-06' AS DATE)
)
-- 第二步：左连接原聚合查询，补全缺失日期的0值
SELECT
    COALESCE(A, 0) AS A,
    COALESCE(B, 0) AS B,
    dr.DateValue AS Time
FROM DateRange dr
LEFT JOIN (
    -- 你的原聚合查询，保留核心逻辑
    SELECT 
        SUM(CASE WHEN Side = 'A' THEN 1 ELSE 0 END) AS A,
        SUM(CASE WHEN Side = 'B' THEN 1 ELSE 0 END) AS B,
        DATEADD(DAY, 0, DATEDIFF(DAY, 0, t_stamp)) AS Time
    FROM Log
    WHERE 
        t_stamp BETWEEN '2018-03-01 06:00:00' AND '2018-04-06 06:00:00'
        AND Bad = 1
    GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, t_stamp))
) agg ON dr.DateValue = agg.Time
ORDER BY dr.DateValue;

关键点说明： #

• 递归CTEDateRange会生成从2018-03-01到2018-04-06的每一天（注意结束日期包含2018-04-06，匹配原查询的结束时间点）。
• LEFT JOIN确保所有日期都被保留，原查询没有数据的日期会返回NULL，COALESCE把这些NULL转为0，满足你要的0值展示需求。

方案2：用日期维度表（更高效，适合频繁使用） #

如果你的数据库里有现成的日期维度表（比如DimDate，预先生成了所有日期的表），直接用它来关联会更高效，尤其是时间范围很大的时候：

SELECT
    COALESCE(agg.A, 0) AS A,
    COALESCE(agg.B, 0) AS B,
    dd.DateValue AS Time
FROM DimDate dd
LEFT JOIN (
    -- 原聚合查询逻辑不变
    SELECT 
        SUM(CASE WHEN Side = 'A' THEN 1 ELSE 0 END) AS A,
        SUM(CASE WHEN Side = 'B' THEN 1 ELSE 0 END) AS B,
        DATEADD(DAY, 0, DATEDIFF(DAY, 0, t_stamp)) AS Time
    FROM Log
    WHERE 
        t_stamp BETWEEN '2018-03-01 06:00:00' AND '2018-04-06 06:00:00'
        AND Bad = 1
    GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, t_stamp))
) agg ON dd.DateValue = agg.Time
WHERE dd.DateValue BETWEEN '2018-03-01' AND '2018-04-06'
ORDER BY dd.DateValue;

优势： #

• 日期维度表通常包含各种日期属性（星期、月份、季度等），不仅性能比递归CTE更好，后续有类似统计需求时可以直接复用。
• 避免了递归CTE在超大时间范围下可能出现的性能问题。

注意事项 #

• 确保日期类型匹配：DateRange.DateValue和agg.Time要统一为DATE类型（或一致的日期格式），避免隐式转换导致的关联错误。
• 如果你的SQL环境不是SQL Server（比如MySQL、PostgreSQL），生成日期序列的语法会有差异，但核心逻辑都是生成全量日期 + 左连接聚合结果，调整对应语法即可。

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