如题，早上查询速度还是很快的直到我又关联上了两张表

下面是代码：

select  distinct

eo.sitecode  组织编码 

  ,so.partcode 料号 , p.partname 品名 ,p.PartSpec 规格,

  so.processstep 工序,ps.processstepcode  工序编码 ,ps.ProcessStepName 工序名称,

  eor.IsOutsource 是否委外   

  ,eor.OutsourceSupplier 

  ,mp.partnerName 委外供方

  ,sl.工票数量 工票数量

  ,sl.报工数量 报工数量

  ,isnull(b.物料数量,0) 期初物料

  ,rk.累计入库

  ,ch.出货数量

  ,isnull(b.物料数量,0)+isnull(rk.累计入库,0)-ISNULL(ch.出货数量,0) 期末结存

 from MES_ScatterOrder  so with(nolock) --工票信息表  

 left  join  MES_ExecutiveOrder eo with(nolock) on eo.id=so.ExecutiveOrderID --生产工单

 left  join   MES_ExecutiveOrderRouting eor with(nolock) on eor.mid=so.ExecutiveOrderID  and  eor.ProcessStep =so.ProcessStep --生产工单工艺表

 left  join   MDC_ProcessStep  ps with(nolock) on ps.id=eor.ProcessStepID  --工序信息表

  join mdc_part p with(nolock) on p.partcode=so.partcode

  join MDC_Partner mp with(nolock) on mp.PartnerCode=eor.OutsourceSupplier 

 left join (--获取期初数量

select Partcode 物料编码 ,sum(Qty) 物料数量,convert(varchar(7),so.lastStartTime,120) 开工日期 from MES_ScatterOrder so with(nolock)

where convert(varchar(7),so.LastStartTime,120)=CONVERT(varchar(7),dateadd(month, -1, '${startdate}' ), 20)

group by Partcode,convert(varchar(7),so.LastStartTime,120)

 )b on b.物料编码=so.PartCode --and b.开工日期=convert(varchar(10),so.LastStartTime,120)

 left join (  --获取累计入库数量

select sum(a.累计入库) 累计入库,a.PartCode,a.OutsourceSupplier ,a.SiteCode,a.PartSpec

from( select distinct eo.Qty 累计入库,so.PartCode,eor.OutsourceSupplier,eo.SiteCode,p.PartSpec

    from MES_ScatterOrder  so with(nolock) --工票信息表  

    join  MES_ExecutiveOrder eo with(nolock) on eo.id=so.ExecutiveOrderID --生产工单

    join   MES_ExecutiveOrderRouting eor with(nolock) on eor.mid=so.ExecutiveOrderID  and  eor.ProcessStep =so.ProcessStep --生产工单工艺表

    join mdc_part p with(nolock) on p.partcode=so.partcode

    join MDC_Partner mp with(nolock) on mp.PartnerCode=eor.OutsourceSupplier 

    where convert(varchar(7),so.LastStartTime,120) =convert(varchar(7),'${startdate}' ,120)  --and so.IsDelete='1'

    and eor.OutsourceSupplier!=''

     )a group by a.OutsourceSupplier,a.PartCode,a.SiteCode,a.PartSpec

  )rk on rk.PartCode=so.PartCode and rk.SiteCode=eo.SiteCode and rk.PartSpec=p.PartSpec

  --累计工票数量和报工数量

  left join (

select a.组织,a.料号,a.工序,sum(a.工票数量) 工票数量,sum(a.报工数量) 报工数量,a.OutsourceSupplier from(

select eo.SiteCode 组织,so.PartCode 料号,so.ProcessStep 工序,sum( isnull(so.qty,0)) 工票数量 ,sum(isnull(so.OKQty,0)) 报工数量 ,eor.OutsourceSupplier

from MES_ScatterOrder  so with(nolock)

left  join  MES_ExecutiveOrder eo with(nolock) on eo.id=so.ExecutiveOrderID --生产工单

left  join   MES_ExecutiveOrderRouting eor with(nolock) on eor.mid=so.ExecutiveOrderID  and  eor.ProcessStep =so.ProcessStep --生产工单工艺表

--join MDC_Partner mp with(nolock) on mp.PartnerCode=eor.OutsourceSupplier 

where eor.OutsourceSupplier!=''

and so.ScatterOrderStatus!='delete'

and convert(varchar(7),so.LastStartTime,120)=convert(varchar(7),'${startdate}' ,120)

group by so.Qty,so.OKQty,eo.SiteCode,eor.OutsourceSupplier,so.PartCode,so.ProcessStep

)a

group by a.组织,a.料号,a.工序,a.OutsourceSupplier

  )sl on sl.组织=eo.SiteCode and sl.料号=so.PartCode   and sl.工序=so.ProcessStep and sl.OutsourceSupplier =eor.OutsourceSupplier

  --累计出货数量

  left join (

  select 

--sp.docno 出货单号,

spl.OrderBy_Code 客户编码,spl.OrderBy_ShortName 客户名称,

sum(spl.ShipQtyTU) 出货数量,im.code 物料编码--spl.ItemInfo_ItemCode 物料编码

,bdvt.Description 电镀直出--,sp.BusinessDate 出货日期

from [U9].[XJU9].[dbo].[SM_ShipPlan] sp with(nolock)--出货计划单

left join [U9].[XJU9].[dbo].[SM_ShipPlanLine] spl with(nolock) on sp.id = spl.ShipPlan--出货计划单行 

left join [U9].[XJU9].[dbo].[SM_ShipLine] sl with(nolock) on sl.srcdocno = sp.docno

left join [U9].[XJU9].[dbo].[SM_Ship] ssp with(nolock) on  sl.Ship=ssp.id

left join [U9].[XJU9].[dbo].[CBO_ItemMaster] im with(nolock) on spl.ItemInfo_ItemID= im.ID--物料编码

left join [U9].[XJU9].[dbo].[Base_DefineValue] bdv with(nolock) on bdv.code=sp.DescFlexField_PrivateDescSeg3

left join [U9].[XJU9].[dbo].[Base_ValueSetDef] bvs with(nolock) on bdv.ValueSetDef=bvs.ID and bvs.code='QT-01'

left join [U9].[XJU9].[dbo].[Base_DefineValue_Trl] bdvt with(nolock) on bdvt.ID=bdv.ID

where spl.OrderBy_Code in('10','20','30','40','50','60')

-- and im.code='010198000248'

and convert(varchar(7),ssp.ShipConfirmDate,120) = convert(varchar(7),'${startdate}' ,120)

group by spl.OrderBy_Code,im.code,bdvt.Description,spl.OrderBy_ShortName

  )ch  on ch.物料编码=so.PartCode and ch.电镀直出 = mp.PartnerName

 where 1=1  

  and eo.SiteCode='50'

  --and so.PartCode='010198000248'

  and  eor.IsOutsource =1  --是否委外=1  委外

  and  so.scatterorderstatus not in ('delete')

  and  ps.ProcessStepName='电镀'  --电镀工序

  and  convert(varchar(7),so.LastStartTime,120)= convert(varchar(7),'${startdate}' ,120)

 ${if(len(org) == 0,""," and eo.sitecode  in ('" + org  + "')")}

${if(len(partcode) == 0,""," and  so.PartCode like '%" + partcode  + "%'")}

${if(len(partspec) == 0,""," and  p.PartSpec like '%" + partspec  + "%'")}

${if(len(outsourcesupplier) == 0,""," and  eor.OutsourceSupplier in ('" + outsourcesupplier  + "')")}

  group  by 

  eo.sitecode  ,so.partcode  , p.partname   ,p.PartSpec  ,so.processstep  ,ps.processstepcode    ,ps.ProcessStepName  ,

  eor.IsOutsource  

    ,eor.OutsourceSupplier 

,eo.Qty

,sl.工票数量

,sl.报工数量

,mp.partnerName

,b.物料数量

,rk.累计入库

,ch.出货数量