MySQL中如何将COUNT统计值用于CASE条件？求排查错误

原语句存在的问题 #

• 语法错误：count(*) as total与CASE子句之间缺少逗号，导致SQL解析失败。
• 逻辑引用错误：同一层级的SELECT中无法直接引用别名total，因为别名是在查询结果生成后才生效的，CASE判断时该别名还未被定义。

修正后的写法 #

写法一：直接在CASE中使用count(*) #

select 
    count(*) as total,
    CASE 
        when count(*) > 3 then 'true'
        else 'false'
    end as result
from friendship 
where userId = '1'

写法二：通过子查询先统计总数 #

select 
    total,
    CASE 
        when total > 3 then 'true'
        else 'false'
    end as result
from (
    select count(*) as total 
    from friendship 
    where userId = '1'
) as sub

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