无数据的SQL CASE语句

本文介绍了无数据的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)