求解。。。求解。。。求解

[屌丝小白]

select
isnull(sum(case when cAlarmtype='火警' then 1 else 0 end),0) as F1,
isnull(sum(case when cAlarmtype='故障' then 1 else 0 end),0) as F2,
isnull(sum(case when cAlarmtype='管理信息' then 1 else 0 end),0) as F3
from tab
有需求，当 F1和F2、F3同时等于0的时候就查不出这条数据。
当然我知道这写法不对，但就这意思：where (F1 !=0 AND F2 !=0 AND F3 !=0)
求大神解答，因为我用了分页的存储过程，能在where 里面做判断么？

[發糞塗牆]

要么在case when那里判断，要么在where那里判断

[Andy__Huang]

--换个角度，只要它是其中之一就查出来

SELECT isnull(sum(case when cAlarmtype='火警' then 1 else 0 end),0) as  F1,
	isnull(sum(case when cAlarmtype='故障' then 1 else 0 end),0) as  F2,
	isnull(sum(case when cAlarmtype='管理信息' then 1 else 0 end),0) as  F3
from tab
where cAlarmtype='火警' or cAlarmtype='故障' or cAlarmtype='管理信息'

[發糞塗牆]

啃爹的现在才能回复

[唐诗三百首]

try this,

select F1,F2,F3 from
(select
 isnull(sum(case when cAlarmtype='火警' then 1 else 0 end),0) as  F1,
 isnull(sum(case when cAlarmtype='故障' then 1 else 0 end),0) as  F2,
 isnull(sum(case when cAlarmtype='管理信息' then 1 else 0 end),0) as  F3
 from tab) t
where not (F1=0 and F2=0 and F3=0)

[xxfvba]

引用 4 楼 ap0405140 的回复:

try this,

select F1,F2,F3 from
(select
 isnull(sum(case when cAlarmtype='火警' then 1 else 0 end),0) as  F1,
 isnull(sum(case when cAlarmtype='故障' then 1 else 0 end),0) as  F2,
 isnull(sum(case when cAlarmtype='管理信息' then 1 else 0 end),0) as  F3
 from tab) t
where not (F1=0 and F2=0 and F3=0)

not的用法

[Cloud_Hero]

当 F1和F2、F3同时等于0，表示啥都没查到，所以就查不出这条记录了。