SQL中CASE WHEN块内嵌多个子查询的写法是否正确？

需求与原代码 #

需要实现带两层判断条件的CASE WHEN逻辑块，初始编写的单判断片段如下：

when (
    (
        SELECT count(distinct id)
        FROM sample_table 
        WHERE name_ IN ('sample', 'another_sample') 
        AND "data" ->> 'sample_json' = 'sample_value'
    ) >= 1
AND
    (
        SELECT count(distinct id) from sample_table 
        WHERE my_date > current_date - interval '3 years'
        AND name_ IN ('sample', 'another_sample')
        AND "data" ->> 'sample_json' = 'sample_value'
    ) = 0
) then 100

完整CTE代码如下：

sample_cte as (
        select
            ot.id_customer_sample,
            ( case
when (
    (
        SELECT count(distinct id)
        FROM sample_table 
        WHERE name_ IN ('sample', 'another_sample') 
        AND "data" ->> 'sample_json' = 'sample_value'
    ) >= 1
AND
    (
        SELECT count(distinct id) from sample_table 
        WHERE my_date > current_date - interval '3 years'
        AND name_ IN ('sample', 'another_sample')
        AND "data" ->> 'sample_json' = 'sample_value'
    ) = 0
) then 100
when (
    select count(distinct id)
    from sample_table
    where my_date > current_date - interval '3 years'
    and (name_='sample' and "data" ->> 'sample_json' = 'sample_value')
    or (name_ = 'another_sample' and "data" ->> 'status' = 'sample_value')) >= 3 then 300
when (
    select count(distinct id)
    from sample_table
    where my_date > current_date - interval '3 years'
    and (name_='sample' and "data" ->> 'sample_json' = 'sample_value')
    or (name_ = 'another_sample' and "data" ->> 'status' = 'sample_value')) >= 1 then 200
else 0 end
) as score_sample

from sample_table cd
right join other_table ot on
ot.id_customer_sample = cd.id_customer_sample
and (cd.name_ = 'sample' and cd."data" ->> 'sample_json' = 'sample_value')
or (cd.name_ = 'another_sample' and cd."data" ->> 'status' = 'sample_value')
group by bp.id_customer_sample

执行后返回结果不符合预期，需要确认写法正确性。

原代码存在的明确问题 #

• 运算符优先级错误：SQL中AND优先级高于OR，原代码WHERE、JOIN条件中and (A) or (B)的写法未给OR整体加括号，会被解析为(前置所有AND条件 + A) OR B，过滤范围完全偏离预期。
• 子查询未做关联：CASE语句中所有count子查询都是全表统计，没有和外层当前行的id_customer_sample做关联，最终得到的计数是全表总计数，不是单个客户维度的统计值，这是结果错误的核心原因。
• 别名引用错误：末尾GROUP BY使用的bp.id_customer_sample不存在，代码中定义的表别名只有cd和ot，执行时会直接抛出别名不存在的错误。
• 性能冗余：重复三次编写逻辑高度相似的统计查询，会重复扫描表数据，执行效率极低。

修正后参考代码 #

通过一次条件聚合提前算出每个客户的所有统计值，避免重复扫表和关联子查询问题，同时修正所有逻辑括号、别名错误：

sample_cte as (
    select
        ot.id_customer_sample,
        case 
            when total_match_cnt >= 1 and recent_3y_match_cnt = 0 then 100
            when recent_3y_valid_cnt >= 3 then 300
            when recent_3y_valid_cnt >= 1 then 200
            else 0 
        end as score_sample
    from other_table ot
    left join (
        select 
            id_customer_sample,
            -- 统计全量匹配记录数
            count(distinct case 
                when name_ in ('sample','another_sample') and "data"->>'sample_json' = 'sample_value' 
                then id 
            end) as total_match_cnt,
            -- 统计近3年匹配记录数
            count(distinct case 
                when my_date > current_date - interval '3 years' 
                and name_ in ('sample','another_sample') 
                and "data"->>'sample_json' = 'sample_value' 
                then id 
            end) as recent_3y_match_cnt,
            -- 统计近3年符合另一套规则的有效记录数
            count(distinct case 
                when my_date > current_date - interval '3 years' and (
                    (name_='sample' and "data"->>'sample_json' = 'sample_value')
                    or (name_='another_sample' and "data"->>'status' = 'sample_value')
                ) then id 
            end) as recent_3y_valid_cnt
        from sample_table
        group by id_customer_sample
    ) cd on ot.id_customer_sample = cd.id_customer_sample
)

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