无数据的SQL CASE语句 [英] SQL CASE Statement for no data

问题描述

我有一个表，其中包含具有运行状态的流程引擎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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！