Select sum(case isnull(inwork,0) when 0 then 1 else 0 end) as cnt1,
sum(case isnull(inwork,0) when 1 then 1 else 0 end) as cnt2,
sum(case isnull(inwork,0) when 2 then 1 else 0 end) as cnt3
from employee
where isnull(inOffice, 0)=1 and substring(SecurityOption,8,1)='1'
sum(case isnull(inwork,0) when 1 then 1 else 0 end) as cnt2,
sum(case isnull(inwork,0) when 2 then 1 else 0 end) as cnt3
from employee
where isnull(inOffice, 0)=1 and substring(SecurityOption,8,1)='1'
sum(case inwork when 1 then 1 else 0 end) as cnt2,
sum(case inwork when 2 then 1 else 0 end) as cnt3
from employee
where isnull(inOffice, 0)=1 and substring(SecurityOption,8,1)='1'
sum(case isnull(inwork,0) when 1 then 1 else 0 end) 是什麼意思??
select
(select Count(inwork) as Quantity from employee where isnull(inOffice, 0)=1
and isnull(inwork, 0)=2 and substring(SecurityOption,8,1)='1' ) a,
(select Count(inwork) as Quantity from employee where isnull(inOffice, 0)=1
and isnull(inwork, 0)=0 and substring(SecurityOption,8,1)='1' ) b,
(select Count(inwork) as Quantity from employee where isnull(inOffice, 0)=1
and isnull(inwork, 0)=1 and substring(SecurityOption,8,1)='1' ) c如與上面馬可給的答案, 各查詢一次, 效率相差不大, 但如多次查詢, 馬可的答案就顯得效率高了很多, 而我這句, 每次查詢時間總是不變, 請問是什麼原因??
就是统计 isnull(inwork,0) = 1 的记录的条数啊。
select Quantity=sum(case when isnull(inwork, 0)=0 then 1 else 0 end)
,Quantity1=sum(case when isnull(inwork, 0)=1 then 1 else 0 end)
,Quantity2=sum(case when isnull(inwork, 0)=2 then 1 else 0 end)
from employee
where isnull(inOffice, 0)=1 and substring(SecurityOption,8,1)='1'