select a.*,b.ServiceNum,f. bjl,c.BatchNum,e.BatchGreaterThan30 , d.LessThan5s
from
(--获取部门名称
select Cwno,DeptName
from GkjlL_His_His a
inner join
OPENDATASOURCE('SQLOLEDB','Data Source=172.17.240.93;User ID=sa;Password=!QAZ2wsx').pddbgz.dbo.[YwCk] e
on a.Cwno = e.Wno
GROUP BY DeptName,a.Cwno) a
inner join ( --计算窗口服务人次
select Cwno, count(0) ServiceNum
from GkjlL_His_His
where Cdate >= '2012-04-01'
and Cstate = '处理完毕'
and Btime != ''
group by Cwno) b on a.Cwno = b.Cwno
inner join (
--计算批量次数
select Cwno, count(0) BatchNum
from GkjlL_His_His
where Cdate >= '2012-04-01'
and Cstate = '处理完毕'
and Btime != ''
and BatchCnt != 0
group by Cwno) c on a.Cwno = c.Cwno
inner join (
--计算小于5秒人数
select Cwno, count(0) LessThan5s
from GkjlL_His_His
where Cdate >= '2012-04-01'
and Cstate = '处理完毕'
and Btime != ''
and DATEDIFF(S, Btime, Etime) < 5
group by Cwno) d on a.Cwno = d.Cwno
inner join
(--计算批量号>30次数
select Cwno,count(0) BatchGreaterThan30
from GkjlL_His_His
where Cdate >= '2010-04-01'
and Cstate='处理完毕'
and Btime!=''
and BatchCnt>'30'
group by Cwno) e on a.Cwno = e.Cwno
inner join
(--计算办件量
select a.Cwno ,sum(BatchCnt)+count(0)-b.BatchNum bjl
from GkjlL_His_His a
inner join
(--计算批量次数
select Cwno, count(0) BatchNum
from GkjlL_His_His
where Cdate >= '2012-04-01'
and Cstate = '处理完毕'
and Btime != ''
and BatchCnt != 0
group by Cwno) b on a.Cwno = b.Cwno
where Cdate >= '2010-04-01'
and Cstate='处理完毕'
and Btime!=''
group by a.Cwno,b.BatchNum )
f on a.Cwno = f.Cwno
每个join 都重复查询了一部分数据，哪位大神可以帮忙给个优化方案？先谢过啦