SQL查询：基于City与Transaction表实现城市无年度销售时销售额为0

核心实现逻辑 #

要覆盖所有城市每一年度的销售额记录、对无销售的情况补0，核心是先构造维度全量交叉集合，再匹配实际业务数据，不能直接基于已有稀疏的销售表做关联：

• 从City表提取所有全量城市的独立列表
• 从Transaction表提取所有出现过的年度的独立列表
• 对两个列表做交叉连接，生成「城市-年度」的全量基准框架，从根源上覆盖所有可能的组合，避免遗漏无销售的场景
• 将基准框架左关联实际销售数据，把关联后为空的销售额值统一替换为0

通用可兼容SQL代码 #

WITH
-- 提取全量城市列表
all_cities AS (
    SELECT DISTINCT City FROM City
),
-- 提取所有存在销售记录的年度列表
all_years AS (
    SELECT DISTINCT Year FROM Transaction
)
SELECT
    c.City,
    y.Year,
    -- 无匹配销售记录时将NULL替换为0
    COALESCE(t.`Total sales`, 0) AS `Total sales`
FROM all_cities c
CROSS JOIN all_years y -- 生成城市与年度的全量组合
LEFT JOIN Transaction t
    ON c.City = t.City
    AND y.Year = t.Year
ORDER BY y.Year, c.CityKey;

样例数据输出验证 #

针对题目给出的示例数据，上述SQL执行后的输出完全匹配需求：

常见踩坑提醒 #

• 注意不要直接将City表左连Transaction表，这种写法只会保证城市本身不缺失，但不会自动补全每个城市对应的所有年度组合，最终结果会直接漏掉2020年Dallas、San Francisco的记录行，无法实现全量覆盖要求
• 关联Transaction表时，必须同时将City、Year两个字段作为匹配条件，否则会出现销售数据错配、结果集重复膨胀的问题
• COALESCE是通用的空值替换函数，所有主流数据库（MySQL、PostgreSQL、SQL Server、ClickHouse）都原生支持，如果使用SQL Server也可以替换为ISNULL实现相同效果
• 如果需要生成指定连续年度范围（哪怕某一年度所有城市都没有销售也要展示），只需要将all_years CTE中的年度列表替换为自定义的连续年度清单即可

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