SQLite无FULL OUTER JOIN时实现年度金额SUM汇总视图

针对你的需求，由于SQLite不支持FULL OUTER JOIN，可以通过CTE（公共表表达式）+ UNION + LEFT JOIN的组合方式实现目标视图，具体方案如下：

核心思路 #

1. 预先分别统计两个表各年份的金额总和，避免重复计算
2. 通过UNION获取所有在T_A或T_B中出现过的年份（自动去重）
3. 以所有年份为基础，左连接两个汇总结果，用COALESCE将无数据的年份总和转为0

创建视图的SQL语句 #

CREATE VIEW Sums AS
WITH 
sum_a AS (
    SELECT year_A AS year, SUM(amount_A) AS sum_a
    FROM T_A
    GROUP BY year_A
),
sum_b AS (
    SELECT year_B AS year, SUM(amount_B) AS sum_b
    FROM T_B
    GROUP BY year_B
),
all_years AS (
    SELECT year FROM sum_a
    UNION
    SELECT year FROM sum_b
)
SELECT 
    ay.year,
    COALESCE(sa.sum_a, 0.0) AS "SUM(amount_A)",
    COALESCE(sb.sum_b, 0.0) AS "SUM(amount_B)"
FROM all_years ay
LEFT JOIN sum_a sa ON ay.year = sa.year
LEFT JOIN sum_b sb ON ay.year = sb.year
ORDER BY ay.year;

效果验证 #

执行该视图后，将得到预期结果：

year   | SUM(amount_A) | SUM(amount_B) 
------------------------------------------
  2020   |      300.0    |        0.0    
  2021   |      700.0    |     3000.0    
  2022   |        0.0    |     7000.0    

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