本文介绍了无数据的SQL CASE语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,其中包含具有运行状态的流程引擎1、2、3、4、5、6。当其中一个引擎出现故障时,该记录将从表中删除。使用CASE语句,我可以显示第一个出现故障的引擎,但是如果有两个或多个引擎出现故障,我该如何开始显示这些引擎呢?例如,如果两个引擎都停机,我如何使此查询显示PE 2停机和PE 4停机。目前,它仅显示列表中关闭的第一个引擎。
SELECT CASE WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 1) = 0 THEN 'PE 1 IS DOWN' WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 2) = 0 THEN 'PE 2 IS DOWN' WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 3) = 0 THEN 'PE 3 IS DOWN' WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 4) = 0 THEN 'PE 4 IS DOWN' WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 5) = 0 THEN 'PE 5 IS DOWN' WHEN (SELECT COUNT(PE_ID) FROM CWVMINFO WHERE PE_ID = 6) = 0 THEN 'PE 6 IS DOWN' ELSE 'ALL PROCESS ENGINES ARE UP AND RUNNING' END "STATUS" from dual; 推荐答案而不是Case,对两种不同的情况使用union all,都很好,有些不运行。子查询分解以减少重复代码。
with engines as (select level as engine_number from dual connect by level <= 6) , down_engines as (select engine_number from engines where engine_number not in (select pe_id from cwvminfo)) select to_char(engine_number) || ' IS DOWN' from down_engines union all select 'all engines are running' from dual where not exists (select null from down_engines)更多推荐
无数据的SQL CASE语句
发布评论