2014-6-27-统计查询

1使用outer apply统计查询

select * from
(select COUNT(1) 'all' from t_smsphone where sendtime>='2014-01-01 18:26:04.803' and sendtime<=GETDATE())a
outer apply
(select COUNT(1) 'succ' from t_smsphone where flag=1 and sendtime>='2014-01-01 18:26:04.803' and sendtime<=GETDATE())b
outer apply
(select COUNT(1) 'fail' from t_smsphone where flag=2 and sendtime>='2014-01-01 18:26:04.803' and sendtime<=GETDATE())c
outer apply
(select COUNT(1) 'nores' from t_smsphone where flag=3 and sendtime>='2014-01-01 18:26:04.803' and sendtime<=GETDATE())d

总结：

outer apply是横向连接外部表，outer apply连接的外部表与表之间不存在联系，只是单纯的横向扩充列。比较适合统计的列间无联系的表。

2使用count(case when 条件 then 。。。end)统计查询

demo:

 select
u.sid,st.sname ,
COUNT(case when p.flag!=0 then p.flag end)'all'
,COUNT(case when p.flag=1 then p.flag end)'succ' 
,COUNT(case when p.flag=2 then p.flag end)'fail' 
,COUNT(case when p.flag=3 then p.flag end)'nores'
     from  
SMSManage.dbo.t_users u inner join SMSManage.dbo.t_sms s on u.uid=s.userid  
and u.isdel=s.isdel inner join SMSManage.dbo.t_smsphone p on s.smsid=p.smsid
and s.isdel=p.isdel inner join store_info st on st.sid=u.sid
and st.isdel=u.isdel where  p.isdel=0 and u.sid>0
and p.flag>0 and u.isdel=0 
and u.uid<>27 and p.sendtime>='2009-06-26 00:00:00' and p.sendtime<='2014-06-26 23:59:59' group by u.sid,st.sname

总结：

count(case when ...then ...end)可以在多表连接的查询结果集中根据条件去再统计。