SQL查询中如何仅将单数字0替换为N/A且不修改多位数中的0

问题根因 #

REPLACE 函数会全局替换字符串中所有匹配的字符，只要数值中包含0就会被替换，自然会出现20变成2N/A的问题。你需要的是仅当统计结果等于0的时候才替换为N/A，用条件判断语句即可实现。

解决方案 #

通用SQL写法（兼容所有主流数据库） #

用CASE WHEN做等值判断，仅在统计结果等于0时输出N/A，否则输出原数值：

select 
    left(pdc.semester,6) as Sem1,
    RIGHT(pdc.semester,4) as sem2,
    pdc.semester as Semester,
    CASE WHEN count(pdc.CNIC) = 0 THEN 'N/A' ELSE CAST(count(pdc.CNIC) AS CHAR) END as TotalRegistrations,
    CASE WHEN count(s.studentid) = 0 THEN 'N/A' ELSE CAST(count(s.studentid) AS CHAR) END as TotalRegistered,
    CASE WHEN count(case when r.Grade is not null then 'Appeared' end) = 0 THEN 'N/A' ELSE CAST(count(case when r.Grade is not null then 'Appeared' end) AS CHAR) END as Appeared,
    CASE WHEN count(case when r.Grade='f' then 'fail' end) = 0 THEN 'N/A' ELSE CAST(count(case when r.Grade='f' then 'fail' end) AS CHAR) END as Failed,
    CASE WHEN count(case when r.grade <>'f' then 'pass' end) = 0 THEN 'N/A' ELSE CAST(count(case when r.grade <>'f' then 'pass' end) AS CHAR) END as Passed
from PDC_PreRegistration pdc 
left join  Students s on pdc.Semester=s.CurSemester and s.nic=pdc.CNIC
left join studentresultnet r on s.studentid=r.studentid and s.CurSemester=r.Semester 
left join semester se on se.Semester=r.Semester
where pdc.semester 
in (select CurSemester from students where  batch='PD2110') 
group by pdc.Semester
ORDER BY  sem2,Sem1

MySQL简化写法 #

如果使用MySQL，可以直接用IF函数简化逻辑，数据库会自动处理类型转换：
示例单字段写法：IF(count(pdc.CNIC) = 0, 'N/A', count(pdc.CNIC))，其余字段按同样规则替换即可。

最终运行效果 #

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