本文介紹了無數(shù)據(jù)的SQL CASE語句的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我有一個表，其中包含具有運行狀態(tài)的流程引擎1、2、3、4、5、6。當(dāng)其中一個引擎出現(xiàn)故障時，該記錄將從表中刪除。使用CASE語句，我可以顯示第一個出現(xiàn)故障的引擎，但是如果有兩個或多個引擎出現(xiàn)故障，我該如何開始顯示這些引擎呢？例如，如果兩個引擎都停機(jī)，我如何使此查詢顯示PE 2停機(jī)和PE 4停機(jī)。目前，它僅顯示列表中關(guān)閉的第一個引擎。
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，都很好，有些不運行。子查詢分解以減少重復(fù)代碼。
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)
這篇關(guān)于無數(shù)據(jù)的SQL CASE語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
