如何用SQL返回Table A数据及对应年份Table B剩余公司（值补0）

需求说明 #

现有两张表：

• Table A：包含字段Companyid、ForYear、value，数据如下：

• Table B：包含字段CompanyID、MarketID，数据如下：

需要编写SQL查询，返回Table A的全部记录，同时补充每个年份中存在于Table B但未在Table A出现的公司记录，这类记录的value字段填充为0，最终输出需与示例格式一致。

SQL查询代码 #

WITH all_years AS (
    SELECT DISTINCT ForYear FROM TableA
)
SELECT 
    b.CompanyID,
    ay.ForYear,
    COALESCE(a.value, 0) AS value
FROM all_years ay
CROSS JOIN TableB b
LEFT JOIN TableA a 
    ON a.Companyid = b.CompanyID 
    AND a.ForYear = ay.ForYear
ORDER BY ay.ForYear DESC, b.CompanyID;

代码说明 #

1. 提取所有年份：通过CTE all_years获取Table A中所有不重复的年份（这里是2021和2022）；
2. 生成所有组合：用CROSS JOIN将每个年份与Table B的所有公司配对，得到所有需要覆盖的(公司ID, 年份)组合；
3. 左连接匹配数据：左连接Table A，匹配对应公司和年份的记录，Table A中存在的记录会返回真实value，不存在的则返回NULL；
4. 填充空值：使用COALESCE函数将NULL的value替换为0；
5. 排序输出：按年份倒序、公司ID排序，确保输出顺序与示例一致。

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