请求大神优化SQL语句
select
convert(varchar(100),tmp.Dcrq,102) Dcrq,tmp.[name],tmp.userno,tmp.zc,tmp.wuc,tmp.wanc,tmp.yec,tmp.zc_sk,tmp.wuc_sk,tmp.wanc_sk,tmp.yec_sk,tmp.zc_s,tmp.wuc_s,tmp.wanc_s,tmp.yec_s,tmp.ExceptionCount,
(case when isnull(ExceptionCount,0)=0 then 0 else isnull(ExceptionCount,0)*10-10 end) 'ActionMoney'
--into #temp
from(select temp.Dcrq,temp.[name],temp.userno,temp.zc,temp.wuc,temp.wanc,temp.yec,temp.zc_sk,temp.wuc_sk,temp.wanc_sk,temp.yec_sk,
case when temp.zc= temp.zc_sk then '正常' else '<strong><span style=''background:black;color:#009900;''>异常</span></strong>' end 'zc_s',
case when temp.wuc= temp.wuc_sk then '正常' else '<strong><span style=''background:black;color:#009900;''>异常</span></strong>' end 'wuc_s',
case when temp.wanc= temp.wanc_sk then '正常' else '<strong><span style=''background:black;color:#009900;''>异常</span></strong>' end 'wanc_s',
case when temp.yec= temp.yec_sk then '正常' else '<strong><span style=''background:black;color:#009900;''>异常</span></strong>' end 'yec_s',
cai.dbo.GetUserExceptionCount(temp.UserNo,@a) 'ExceptionCount'
from(
select
dc.Dcrq,
(select distinct [name] from cai.dbo.[user] u where u.UserNo=dc.Userno)'name',
dc.UserNo,
zc,
wuc,
wanc,
yec,
case zc_sk when 0 then 1 else 0 end 'zc_sk',
case wuc_sk when 0 then 1 else 0 end 'wuc_sk',
case wanc_sk when 0 then 1 else 0 end 'wanc_sk',
case yec_sk when 0 then 1 else 0 end 'yec_sk'
from cai.dbo.dican dc left join
(
select
tt.UserNo,
--(select [name] from cai.dbo.[user] u where u.UserNo=tt.Userno)'name',
sum(tt.zc_sk) 'zc_sk',
sum(tt.wuc_sk) 'wuc_sk',
sum(tt.wanc_sk) 'wanc_sk',
sum(tt.yec_sk) 'yec_sk'
from
(
select
t.UserNo,
case t.sd when 0 then 0 end 'zc_sk',
case t.sd when 1 then 0 end 'wuc_sk',
case t.sd when 2 then 0 end 'wanc_sk',
case t.sd when 3 then 0 end 'yec_sk'
from
(
select
Dcrq,
UserNo ,
zc ,
wuc ,
wanc ,
yec,
sd
from cai.dbo.dican dc
left join
carddb5.dbo.readtable rt on rt.lb=2 and dc.UserNo = rt.e_bh and DATEDIFF(d, (case when sd<3 then dc.Dcrq else DATEADD(d,+1,dcrq) end) , rt.dtime)=0 where rt.lb=2 and datediff(d, dc.Dcrq,@a)=0
)t group by t.Dcrq,t.UserNo,t.sd
)tt group by tt.UserNo
)ttt on dc.UserNo = ttt.UserNo where DATEDIFF(d,dc.dcrq,@a)=0
union all
select
GETDATE() 'Dcrq',
isnull((select distinct name from cai.dbo.[user] u where u.userno=e_bh),'临时卡')'name',
t.e_bh,
0 'zc',
0 'wuc',
0 'wanc',
0 'yec',
sum(t.zc_sk) 'zc_sk',
sum(t.wuc_sk) 'wuc_sk',
sum(t.wanc_sk) 'wanc_sk',
sum(t.yec_sk) 'yec_sk'
from(
select
e_bh,
case sd when 0 then 1 else 0 end 'zc_sk',
case sd when 1 then 1 else 0 end 'wuc_sk',
case sd when 2 then 1 else 0 end 'wanc_sk',
case sd when 3 then 1 else 0 end 'yec_sk'
from carddb5.dbo.readtable rt where DATEDIFF(d,dtime,@a)=0 and rt.lb=2
and not exists(select dc.id,dc.userno,dc.dcrq,dc.zc,dc.wuc,dc.wanc,dc.yec,dc.xy from cai.dbo.dican dc where dc.UserNo = rt.e_bh and DATEDIFF(d,Dcrq,@a)=0 and rt.lb=2) --and sd <3
group by e_bh,sd
)t group by t.e_bh
)temp)tmp where tmp.userno not in('1585','1396','1215','1606','1498','155','156','157','159','160','152','153','158','151','160','1620','1679','1696','1695') order by tmp.UserNo asc