为什么SQL JOIN查询返回的count统计结果来自错误的表？

问题核心原因 #

你当前写法的统计结果不符合预期，核心原因如下：

• count(*)统计的是内连接合并后结果集的总行数，不是单独统计table_i的行数。当table_c中同一个ai存在多条匹配记录时，每条table_c的记录都会和table_i的对应行做关联，导致结果集行数膨胀，最终统计值会等于匹配到的table_c关联行数，和你预期统计table_i行数的需求不符。
• 举个实际场景：table_i中ai=123的行只有1条，table_c中ai=123的行有4条，内连接后会生成4条关联结果，count(*)返回值就是4，而非你预期的1。

解决方案 #

有两种常用方案可以实现你要统计table_i行数的需求：

1. 统计时对table_i的唯一主键去重
   把count(*)替换为count(DISTINCT i.你的table_i主键字段)，比如table_i的主键是id的话，写法如下：

select i.dt,c.name,count(DISTINCT i.id) 
from table_i i 
join table_c c on i.ai = c.ai 
where i.dt between '20210920' and '20210925' 
and i.id = '45632' 
group by i.dt,c.name;

2. 提前对table_c按关联键去重后再关联
   先将table_c按ai字段去重，避免一行table_i匹配到多行table_c的情况，写法如下：

select i.dt,c.name,count(*) 
from table_i i 
join (select DISTINCT ai,name from table_c) c on i.ai = c.ai 
where i.dt between '20210920' and '20210925' 
and i.id = '45632' 
group by i.dt,c.name;

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