关联子查询去相关后出现重复c_name结果不符合预期的原因排查

问题原因 #

你改写的SQL出现重复记录，核心是笛卡尔积+OR查询条件的逻辑漏洞：

• 用逗号连接customer c1和聚合后的临时表c2本质是笛卡尔积，返回行数初始为两张表的行数乘积
• 当c1行满足c1.c_mktsegment = 'AUTOMOBILE'时，WHERE条件的OR第一分支直接生效，这行c1会和c2临时表的所有行全部匹配成功，临时表有多少个不同的市场分段，这条记录就会重复返回多少次。

正确改写方案 #

方案1：左外连接改写（兼容所有支持标准SQL的数据库） #

将分段匹配逻辑放到JOIN条件中，保证每一条c1记录最多匹配1条c2聚合记录，不会产生笛卡尔积：

select c1.c_name 
from customer c1
left join (
    select c_mktsegment, avg(c_acctbal) as m 
    from customer 
    group by c_mktsegment
) c2 on c1.c_mktsegment = c2.c_mktsegment
where 
    c1.c_mktsegment = 'AUTOMOBILE' 
    or c1.c_acctbal > c2.m;

方案2：窗口函数改写（更简洁，性能更优） #

如果你的数据库支持窗口函数，不需要多表关联即可实现逻辑：

select c_name
from (
    select 
        c_name,
        c_mktsegment,
        c_acctbal,
        avg(c_acctbal) over(partition by c_mktsegment) as segment_avg
    from customer
) t
where c_mktsegment = 'AUTOMOBILE' or c_acctbal > segment_avg;

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