比如我统计一些数据
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
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
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
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
;
改成这样就行了。你参考下