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)可以在多表连接的查询结果集中根据条件去再统计。