如何将两张表的结果按tier合并为单行，无匹配项count补0？

现有两张表，均包含num、tier、count列，仅部分数据存在差异。需求为：当num值匹配时，将两表结果按tier合并为单行；若TABLE_2中无对应tier，则其count设为0。

现有表查询结果 #

TABLE_1 查询结果 #

执行SQL语句：

select tier, count from TABLE_1 where num=1 order by tier

返回结果：

tier  count
0     10
1     1
2     1
3     1
4     1

TABLE_2 查询结果 #

执行SQL语句：

select tier, count from TABLE_2 where num=1 order by tier

返回结果：

tier  count
0     1
1     4
2     10

错误的Inner Join结果 #

若使用仅关联num的内连接，会产生大量冗余数据：

select p.tier, p.count, b.tier, b.count from TABLE_1 p
inner join TABLE_2 b 
on p.num = b.num 
where p.num=1 
order by p.tier

冗余结果示例：

0    10   1    4  
0    10   0    1    
0    10   2    10 
1    1    1    4  
1    1    0    1    
1    1    2    10 
2    1    0    1  
2    1    1    4    
2    1    2    10 
...

期望结果 #

0     10   0  1
1     1    1  4
2     1    2  10
3     1    3  0 
4     1    4  0

注：TABLE_2的tier无需与TABLE_1严格对应，但无匹配tier时count需设为0。

解决方案 #

使用**左连接（LEFT JOIN）**同时关联num和tier，结合COALESCE函数处理缺失值：

select 
    p.tier as t1_tier, 
    p.count as t1_count, 
    COALESCE(b.tier, p.tier) as t2_tier, 
    COALESCE(b.count, 0) as t2_count
from TABLE_1 p
left join TABLE_2 b 
on p.num = b.num and p.tier = b.tier
where p.num=1 
order by p.tier

逻辑说明 #

• 左连接以TABLE_1为基准，保留所有行，仅匹配TABLE_2中num和tier均相同的行
• COALESCE(b.count, 0)：当TABLE_2无匹配行时，自动将count替换为0
• COALESCE(b.tier, p.tier)：确保无匹配tier时，显示TABLE_1的tier值，与期望结果格式一致

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