比如我統計一些資料
select count(*) num, a.alarmlevel
from v_customer_alarminfo_now a
where a.isrepeat = 0
and a.iscleared = 0
and a.alarmlevel in
('minor', 'critical', 'warning', 'indeterminate', 'major')
group by a.alarmlevel
查出的結果為
num alarmlevel
4 major
那怎么才能把查不到的資料回傳0了，比如
num alarmlevel
0 minor
0 critical
0 warning
0 indeterminate
4 major
uj5u.com熱心網友回復：
t1 as
(select 'minor' as ll from dual
union all
select 'warning' from dual
union all
select 'indeterminate' from dual
union all
select 'major' from dual)
select t1.*,count(*) num from
t1 left join v_customer_alarminfo_now a
on t1.ll=a.alarmlevel
where a.isrepeat = 0
and a.iscleared = 0
group by t1.ll
uj5u.com熱心網友回復：
with tmp1 as (select 'minor' as f1 from dual
union all
select 'critical' as f1 from dual
union all
select 'warning' as f1 from dual
union all
select 'indeterminate' as f1 from dual
union all
select 'major' as f1 from dual
)
select b.f1,count(a.alarmlevel) from tmp1 b left join v_customer_alarminfo_now a
on a.alarmlevel =b.f1
where a.isrepeat = 0
and a.iscleared = 0
and a.alarmlevel in
('minor', 'critical', 'warning', 'indeterminate', 'major')
group by b.f1
;
改成這樣就行了。你參考下
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/71821.html
標籤：基礎和管理
上一篇：Oracle中洗掉表空間提示ORA-00604和ORA-21700
下一篇：多表查詢陳述句優化