如题，早上查询速度还是很快的直到我又关联上了两张表下面是代码：select distincteo.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 ... sp with(nolock)--出货计划单 left join ... spl with(nolock) on sp.id = spl.ShipPlan--出货计划单行 left join ... sl with(nolock) on sl.srcdocno = sp.docno left join ... ssp with(nolock) on sl.Ship=ssp.id left join ... im with(nolock) on spl.ItemInfo_ItemID= im.ID--物料编码 left join ... bdv with(nolock) on bdv.code=sp.DescFlexField_PrivateDescSeg3 left join ... bvs with(nolock) on bdv.ValueSetDef=bvs.ID and bvs.code='QT-01' left join ... 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.出货数量
