如何补全SQL查询结果的ID、task列并填充缺失日期小时数为0？

问题的核心是你需要先构建所有ID+Task+星期几的完整组合，再关联原表数据填充小时数——直接用仅含星期几的临时表左联原表，会导致无匹配的星期几丢失ID/Task信息，这就是你出现空值的原因。

步骤说明 #

1. 保留星期几维度的临时表（可去掉hours字段，后续用COALESCE填充0更灵活）
2. 生成所有需要的ID+Task组合（支持单个或多组场景）
3. 将ID+Task组合与星期几表做交叉连接，得到全量维度组合
4. 左联原表数据，用原表小时数覆盖默认值，无匹配时保留0

具体SQL实现 #

1. 修正星期几临时表（可选优化）

DROP TABLE IF EXISTS #dayofweek;
CREATE TEMP TABLE #dayofweek (
    dayofweek VARCHAR(50)
);

INSERT INTO #dayofweek (dayofweek)
VALUES 
('sunday'),('monday'),('tuesday'),('wednesday'),
('thursday'),('friday'),('saturday');

2. 单个ID+Task场景

-- 生成固定的ID+Task组合，交叉连接星期几得到全量维度
WITH base_dim AS (
    SELECT 'A' AS ID, 'jump' AS task
    CROSS JOIN #dayofweek
)
SELECT 
    bd.ID,
    bd.task,
    bd.dayofweek,
    COALESCE(a.hours, 0) AS hours
FROM base_dim bd
LEFT JOIN TableA a 
    ON bd.ID = a.ID 
    AND bd.task = a.task 
    AND bd.dayofweek = a.dayofweek; -- 确保原表星期数字段名与临时表一致，不一致需调整

3. 多个ID+Task通用场景

如果需要覆盖TableA中所有存在的ID+Task组合：

-- 先提取所有唯一的ID+Task组合
WITH id_task_dim AS (
    SELECT DISTINCT ID, task 
    FROM TableA
    -- 可按需添加过滤条件，比如 WHERE task = 'jump'
),
-- 生成全量的ID+Task+星期几组合
full_dim AS (
    SELECT itd.ID, itd.task, dw.dayofweek
    FROM id_task_dim itd
    CROSS JOIN #dayofweek dw
)
SELECT 
    fd.ID,
    fd.task,
    fd.dayofweek,
    COALESCE(a.hours, 0) AS hours
FROM full_dim fd
LEFT JOIN TableA a 
    ON fd.ID = a.ID 
    AND fd.task = a.task 
    AND fd.dayofweek = a.dayofweek;

关键说明 #

• 交叉连接（CROSS JOIN）是核心：它确保每个ID+Task都能对应一周7天的完整维度，避免无匹配日期丢失ID/Task信息
• COALESCE(a.hours, 0)用于处理原表无数据的情况，自动填充0
• 注意原表中存储星期几的字段名需与临时表的dayofweek一致，若不一致需调整关联条件

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