如何优化该SQL语句？（个人觉的应该可以优化很多，但不会做）
update joblist set
s1=(select count(*) from conjoblist as a where
a.SERVERLEVEL='C' and a.jobtype='L' and a.cmdopt=6
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s2=(select count(*) from conjoblist as a where
a.SERVERLEVEL='C' and a.jobtype='L' and a.cmdopt=5 and
a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s3=(select count(*) from conjoblist as a where
a.SERVERLEVEL='C' and a.cmdopt=7
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s4=(select count(*) from conjoblist as a where
a.SERVERLEVEL='C' and a.jobtype='T' and a.cmdopt=1 and a.jobphase='Init' and a.jobstatus='Pause'
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s5=(select count(*) from conjoblist as a where
a.SERVERLEVEL='C' and a.jobtype='T' and a.cmdopt=1 and (a.jobstatus='OK' or a.jobstatus='Sent' or a.jobstatus='SentOK')
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s6=(select count(*) from conjoblist as a where
SERVERLEVEL='C' and jobtype='T' and cmdopt=1 and jobphase='Init' and jobstatus='Error' and BHG=1
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s7=(select count(*) from conjoblist as a where
SERVERLEVEL='C' and jobtype='T' and cmdopt=2
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s8=(select count(*) from conjoblist as a where
SERVERLEVEL='C' and cmdopt=0
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s9=(select count(*) from conjoblist as a where
SERVERLEVEL='C' and cmdopt=2
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s10=(select count(*) from conjoblist as a where
SERVERLEVEL='C' and jobstatus='Hit' and cmdopt=0
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s11=(select count(distinct caseno) from conjoblist as a where
SERVERLEVEL='C' and jobstatus='Hit' and cmdopt=5
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
,s14=(select count(distinct caseno) from conjoblist as a where
SERVERLEVEL='C' and jobtype='L' and cmdopt=6
and a.xzqh=joblist.xzqh and datediff(day,a.starttime,joblist.starttime)=0)
问题点数：20、回复次数：4Top
1 楼summerICEREDTEA（从基础学起）回复于 2005-08-04 20:23:18 得分 0
-.-
估计我也就写出这程度来
MARKTop
2 楼guid6（学无止境）回复于 2005-08-04 20:50:14 得分 0
可以尝试用游标或循环Top
3 楼bugchen888（臭虫）回复于 2005-08-04 21:15:25 得分 0
UPDATE joblist
SET s1 = c.s1,
s2 = c.s2,
s3 = c.s3,
s4 = c.s4,
s5 = c.s5,
s6 = c.s6,
s7 = c.s7,
s8 = c.s8,
s9 = c.s9,
s10 = c.s10,
s11 = (SELECT COUNT (DISTINCT caseno)
FROM conjoblist a
WHERE serverlevel = 'C'
AND jobstatus = 'Hit'
AND cmdopt = 5
AND a.xzqh = joblist.xzqh
AND datediff (DAY, a.starttime, joblist.starttime) = 0),
s14 = (SELECT COUNT (DISTINCT caseno)
FROM conjoblist a
WHERE serverlevel = 'C'
AND jobtype = 'L'
AND cmdopt = 6
AND a.xzqh = joblist.xzqh
AND datediff (DAY, a.starttime, joblist.starttime) = 0)
FROM ( SELECT
s1 = sum(case when(a.jobtype = 'L' AND a.cmdopt = 6)then 1 else 0 end),
s2 = sum(case when(a.jobtype = 'L' AND a.cmdopt = 5)then 1 else 0 end),
s3 = sum(case when(a.cmdopt = 7 then 1 else 0 end),
s8 = sum(case when(cmdopt = 0 then 1 else 0 end),
s9 = sum(case when(cmdopt = 2 then 1 else 0 end),
s7 = sum(case when(jobtype = 'T' AND cmdopt = 2)then 1 else 0 end),
s10 = sum(case when(jobstatus = 'Hit' AND cmdopt = 0)then 1 else 0 end),
s4 = sum(case when(a.jobtype = 'T'
AND a.cmdopt = 1
AND a.jobphase = 'Init'
AND a.jobstatus = 'Pause')then 1 else 0 end),
s5 = sum(case when(a.jobtype = 'T'
AND a.cmdopt = 1
AND ( a.jobstatus = 'OK'
OR a.jobstatus = 'Sent'
OR a.jobstatus = 'SentOK'
))then 1 else 0 end),
s6 = sum(case when(jobtype = 'T'
AND cmdopt = 1
AND jobphase = 'Init'
AND jobstatus = 'Error'
AND bhg = 1)then 1 else 0 end),
FROM conjoblist a
WHERE a.serverlevel = 'C'
AND a.xzqh = joblist.xzqh
AND datediff (DAY, a.starttime, joblist.starttime) = 0) cTop
4 楼bugchen888（臭虫）回复于 2005-08-04 21:17:45 得分 0
s3 = sum(case when(a.cmdopt = 7 then 1 else 0 end),
s8 = sum(case when(cmdopt = 0 then 1 else 0 end),
s9 = sum(case when(cmdopt = 2 then 1 else 0 end),
改为
s3 = sum(case when a.cmdopt = 7 then 1 else 0 end),
s8 = sum(case when a.cmdopt = 0 then 1 else 0 end),
s9 = sum(case when a.cmdopt = 2 then 1 else 0 end),Top
