select sum(case when fsalary>9999 and fage > 35 then 1 else 0 end),
       sum(case when fsalary>9999 and fage < 35 then 1 else 0 end),
       sum(case when fsalary<9999 and fage > 35 then 1 else 0 end),
       sum(case when fsalary<9999 and fage < 35 then 1 else 0 end)
from empinfo

------解决方案--------------------
这条语句和你的执行计划效率一样   declare @total int
 declare @fs_less_9999 int
 declare @fa_less_35 int
 declare @ff_less_9999_35 int
 select @total =(select COUNT(1) as total from empinfo),
 @fs_less_9999=( select COUNT(1) as fs_less_9999 from empinfo where Fsalary <9999),
 @fa_less_35 =(select COUNT(1) as fa_less_35 from empinfo where Fage  <35),
 @ff_less_9999_35 =(  select COUNT(1) as ff_less_9999_35 from empinfo where Fsalary <9999 and Fage <35)
  
 print('fasalary<9999 and fage<35 count is:'+cast (@ff_less_9999_35 as varchar(10))
 +' fasalary>9999 and fage<35 count is:'+cast (@fa_less_35-@ff_less_9999_35 as varchar(10))
 +' fasalary<9999 and fage>35 count is:'+cast (@fs_less_9999-@ff_less_9999_35 as varchar(10))
 +' fasalary>9999 and fage>35 count is:'+cast (@total-@fa_less_35-@fs_less_9999+@ff_less_9999_35 as varchar(10))
 )

------解决方案--------------------
这条用游标写的……执行计划的评估也是一样，当然是在无数据的情况下，如果有1000万的数据，可能计划会不一样 --n1 n2 n3 n4 分别表示fasalary<9999 and fage<35、fasalary>9999 and fage<35、fasalary>9999 and fage>35、fasalary<9999 and fage>35的员工数
 declare @n1 int=0,@n2 int=0,@n3 int=0,@n4 int=0,@fs int=0,@fa int=0
 declare cs cursor for select Fsalary,Fage from  empinfo option (table hint(empinfo) )
 open cs;
 fetch next from cs into @fs,@fa
 while(@@FETCH_STATUS =0)
 begin
 if(@fs<9999)
 begin
	if(@fa <35)
	set @n1=@n1+1;
	else
	set @n2=@n2+1;
 end
 else  
 begin
 if(@fa<35)
 set @n4=@n4+1;
 else
 set @n3=@n3+1; 
 end 
 fetch next from cs into @fs,@fa 
 end
    
 close cs;
 deallocate cs;
   print('fasalary<9999 and fage<35 count is:'+cast (@n1 as varchar(10))
 +' fasalary>9999 and fage<35 count is:'+cast (@n2 as varchar(10))
 +' fasalary>9999 and fage>35 count is:'+cast (@n3 as varchar(10))
 +' fasalary<9999 and fage>35 count is:'+cast (@n4 as varchar(10)) 
 )


------解决方案--------------------
这条使用临时表做的……执行计划和你的相比为38%，主要用在一次全表扫描和计算中（这是我最想看到的结果），但是临时表可能会耗费大量的空间
declare @n1 int=0,@n2 int=0,@n3 int=0,@n4 int=0,@fs int=0,@fa int=0,@i int=1,@total int=0;
 --先执行一下不然评估计划出不来select row_number() over(order by Fempno) as rid ,Fsalary,Fage into #temp from empinfo&