查询不存在的case_no时返回0的根因分析及实现方案

表结构与问题重现 #

原表结构 #

case_no    history_no
A22010021   1
A22010021   2
A22010021   3

原SQL语句 #

select
    case 
        when max(history_no) is null 
            then 0 
        else max(history_no) end as max
from
    table
where
    case_no = 'A22010022' 
group by
    case_no

问题现象 #

• 查询存在的case_no='A22010021'时，返回结果3，符合预期；
• 查询不存在的case_no='A22010022'时，返回结果为Null，不符合返回0的期望。

问题根因 #

当WHERE case_no = 'A22010022'没有匹配到任何行时，GROUP BY case_no不会生成任何分组，整个查询返回空结果集（0行数据）。你看到的max列显示Null，是客户端对空结果集的默认展示，而非SQL返回了一行值为Null的数据。此时CASE判断逻辑根本没有执行的机会——因为没有行被输出。

实现方法 #

方法1：移除GROUP BY，直接使用聚合函数 #

单独使用聚合函数（如MAX）时，即使没有匹配行，也会返回一行Null，此时CASE语句可以正常将Null转为0。

select
    case 
        when max(history_no) is null 
            then 0 
        else max(history_no) end as max
from
    table
where
    case_no = 'A22010022'

方法2：用UNION ALL结合NOT EXISTS生成默认行 #

先尝试查询匹配数据，若没有结果则返回默认的0行。不同数据库的写法略有差异：

• Oracle版本：

select max(history_no) as max
from table
where case_no = 'A22010022'
union all
select 0 as max
from dual
where not exists (
    select 1 from table where case_no = 'A22010022'
)

• MySQL版本：

select max(history_no) as max
from table
where case_no = 'A22010022'
union all
select 0 as max
from (select 1) as temp
where not exists (
    select 1 from table where case_no = 'A22010022'
)

• SQL Server版本：

select max(history_no) as max
from table
where case_no = 'A22010022'
union all
select 0 as max
where not exists (
    select 1 from table where case_no = 'A22010022'
)

方法3：LEFT JOIN目标case_no临时表 #

构造包含目标case_no的临时表，左连接原表后用COALESCE函数直接处理Null值，简化逻辑。

select
    coalesce(max(t.history_no), 0) as max
from
    (select 'A22010022' as case_no) as target
left join `table` t on target.case_no = t.case_no
group by target.case_no

注：如果你的表名是table（关键字），需要用反引号（MySQL）或方括号（SQL Server）包裹。

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