这样的子查询应该怎么避免

Question

select Spreader,counts, (select COUNT(*) from view_Login where [loginNum]>0 and Spreader=v.Spreader) as one ,(select COUNT(*) from view_Login where [loginNum]>1 and Spreader=v.Spreader) as two ,(select COUNT(*) from view_Login where [loginNum]>2 and Spreader=v.Spreader) as three from view_Login as v group by Spreader,counts view_Login 是推广登录的日志表 Spreader 是推广员counts 是推广人数uid 是用户IDloginNum 是登录数现在要统计推广用户登录的情况，最后写出这样一个粗糙的SQL语句，应该还有更好的写法，特求

Answer 1

使用case  when 转换

select 

Spreader,counts
,SUM(case when  [loginNum]>0  then 1 else 0 end) one

,SUM(case when  [loginNum]>1   then 1 else 0 end )  two

,SUM(case when  [loginNum]>2   then 1 else 0 end ) three

from view_Login as v
            group  by Spreader,counts

Answer 2

顶！！！！！！！！！！！