34,838
社区成员




--以下代码16秒,查两次union的方式
select a.spid,a.notes,a.progtype,a.feevalue/100 as feevalue,b.mt_content,b.id ,num1,num2 from (
select spid,mtype,num1=isnull(count(distinct mobile),0) from v_mr
where createtime>='2011-11'+'-01' and stat='DELIVRD'
group by spid,mtype) m
join (
select spid,mtype,num2=isnull(count(distinct mobile),0) from v_mr
where createtime>='2011-10'+'-01' and stat='DELIVRD'
group by spid,mtype
) n on (m.spid=n.spid and m.mtype=n.mtype)
join esms_limit a on (a.spid=m.spid and a.type=m.mtype)
join esms_spinfo b on (a.spid=b.spid)
--以下代码将近3分钟,为了可读性
select a.spid,a.notes,a.progtype,a.feevalue/100 as feevalue,b.mt_content,b.id ,num1,num2 from (
select spid,mtype,
num1=isnull(count(distinct case when createtime>='2011-11'+'-01' and stat='DELIVRD' then mobile end),0),
num2=isnull(count(distinct case when createtime>='2011-10'+'-01' and stat='DELIVRD' then mobile end),0)
from v_mr
group by spid,mtype
) m
join esms_limit a on (a.spid=m.spid and a.type=m.mtype)
join esms_spinfo b on (a.spid=b.spid)
简单说下需求:就是查最近1个月的数据和最近2个月的数据,再和2个配置表连接。
索引spid啊,createtime啊,stat啊之类的上面该建的都建了。
主要是想一条语句让页面显示出来这些数据,这个速度还是等不急。
太长不好看,又不太想用存储过程,不知道还有什么地方可以优化不,求大神指教