为数据表补全指定日期缺失CODE对应的X/Y/Z列0值虚拟行

核心思路 #

先构建所有日期与CODE的完整组合，保证每个日期下所有CODE都对应一行，再将这个组合与原表左关联，最后把缺失的X、Y、Z字段值替换为0。

SQL实现示例 #

假设原表名为table_a，字段为DATE、CODE、X、Y、Z：

1. 生成日期与CODE的全量组合 #

先提取原表中所有唯一日期和唯一CODE，生成笛卡尔积：

WITH all_combinations AS (
    SELECT 
        d.DATE, 
        c.CODE
    FROM 
        (SELECT DISTINCT DATE FROM table_a) d
    CROSS JOIN 
        (SELECT DISTINCT CODE FROM table_a) c
)

2. 左连接原表并填充0值 #

将全量组合与原表左连接，用COALESCE替换空值为0：

SELECT 
    ac.DATE,
    ac.CODE,
    COALESCE(t.X, 0) AS X,
    COALESCE(t.Y, 0) AS Y,
    COALESCE(t.Z, 0) AS Z
FROM 
    all_combinations ac
LEFT JOIN 
    table_a t ON ac.DATE = t.DATE AND ac.CODE = t.CODE
ORDER BY 
    ac.DATE, ac.CODE;

关键说明 #

• CROSS JOIN确保覆盖所有日期与CODE的可能配对，不会遗漏需要补全的行
• COALESCE函数自动取第一个非空值，原表中缺失的字段会被替换为0
• 最终按日期、CODE排序，与目标输出结构一致

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