同表内连接查询count()结果错误问题排查

你的核心问题是错误使用自连接导致笛卡尔积，使得count统计的是关联后的总行数，而非目标的原表记录数。

错误原因拆解 #

拿你的SQL语句1举例：

select t1.name, max(t1.score), min(t1.score), count(*) from 
t1 join t1 as t2 on t1.name=t2.name 
where t2.rowid > 9 group by t2.name;

• 你通过t1 join t2 on t1.name=t2.name让所有同name的t1记录和符合t2.rowid>9的t2记录做关联，产生了笛卡尔积：
  • t2中james符合条件的有2条（rowid=10、11）
  • t1中james总共有8条
  • 关联后总行数是8×2=16，这就是你得到count(*)=16的原因

SQL语句2同理：

• t2中james符合rowid>5的有4条，t1中james8条，8×4=32
• t2中peter符合rowid>5的有2条，t1中peter3条，3×2=6

正确写法 #

根据你的预期结果，你需要的是针对有新插入行（rowid超过阈值）的name，计算该name在全表中的min/max和总记录数，正确的SQL应该先筛选出有新行的name，再基于这些name做全表统计：

对应SQL语句1的修正：

select name, max(score), min(score), count(*)
from t1
where name in (
    select distinct name from t1 where rowid > 9
)
group by name;

执行结果：james, 7, 0, 8，符合你的预期。

对应SQL语句2的修正：

select name, max(score), min(score), count(*)
from t1
where name in (
    select distinct name from t1 where rowid > 5
)
group by name;

执行结果：

james, 7, 0, 8
peter, 8, 0, 3

完全匹配你的预期。

补充说明 #

如果你的需求是仅统计新插入行的min/max和数量（而非全表），那直接过滤rowid阈值即可，不需要子查询：

select name, max(score), min(score), count(*)
from t1
where rowid > 9
group by name;

结果会是james,7,7,2，适合只更新新行自身的统计值场景。

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