sql-server - 即使值不匹配，两个表中的列总和？

Question

以下使用内部联接的查询返回name两个表中匹配的总和。

;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 cte1.name, cte1.total + cte2.total as 'total'
from cte1 inner join cte2 on
cte1.name = cte2.name

结果是：

name    total   total   total
a       1       11      12
b       2       22      24
y       7       7       14
z       8       8       16

即使没有匹配，我也需要显示总数，因此结果应如下所示（“n/a”只是显示 cte 中不存在值）：

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

值“x”和“f”不在 cte2 中，但它们包含在总数中。'd' 不在 cte1 中，但我们也看到了总数。

标签： sql-servertsql

Answer 1

您可以使用完全外连接尝试以下操作，例如

;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, 
    cte1.total as cte1total , 
    cte2.total as cte2total, 
    coalesce(cte1.total,0) + coalesce(cte2.total,0) as 'total'
from cte1 full outer join cte2 on
cte1.name = cte2.name

查看工作演示 db fiddle

姓名	cte1 总计	cte2 总计	全部的
一个	1	11	12
b	2	22	24
F	30		30
X	6		6
是的	7	7	14
z	8	8	16
d		6	6