SQL如何实现两表按name关联列求和，同时包含所有无匹配记录

解决方案 #

你需要用FULL OUTER JOIN（全外连接）替代原有的INNER JOIN，同时处理空值转换即可实现需求，完整SQL如下：

;with cte1 as
(
    select 'a' as 'name', 1 as 'total'
    union
    select 'b', 2
    union
    select 'x', 6
    union
    select 'y', 7
    union
    select 'z', 8
    union
    select 'f', 30
),
cte2 as
(
    select 'a' as 'name', 11 as 'total'
    union
    select 'b', 22
    union
    select 'd', 6
    union
    select 'y', 7
    union
    select 'z', 8
)
select 
    COALESCE(cte1.name, cte2.name) as name,
    ISNULL(CAST(cte1.total AS VARCHAR(10)), 'n/a') as [cte1.total],
    ISNULL(CAST(cte2.total AS VARCHAR(10)), 'n/a') as [cte2.total],
    ISNULL(cte1.total, 0) + ISNULL(cte2.total, 0) as total
from cte1 
full outer join cte2 on cte1.name = cte2.name

逻辑说明 #

• 全外连接可以同时保留左表(cte1)和右表(cte2)的所有行，不受另一边匹配结果的限制
• COALESCE(cte1.name, cte2.name) 自动取两个CTE中存在的name值，避免出现空的name列
• 用ISNULL函数处理空值，不存在的total字段转换为n/a展示（需要先将数值类型转换为字符串类型避免类型冲突）
• 合计值计算时将空的total默认按0处理，保证求和结果正确

执行结果 #

name cte1.total cte2.total total
---- ---------- ---------- -----------
a    1          11         12
b    2          22         24
f    30         n/a        30
x    6          n/a        6
y    7          7          14
z    8          8          16
d    n/a        6          6

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