BYR Achieve · 镜像论坛

insert into SCRB_YSRB_MX select '1000','20061208',vn.CDBH ,vn.生产车数,vn.平均吨位, vn.生产车数+ mx1.在册累计车数 as 累计车数 ,yjh.完好率月计划,wh2.当日,wh2.当日率,wh2.当日+ mx1.完好累计车日,wh2.当日/(wh2.当日+ mx1.完好累计车日) ,yjh.工作率月计划,gz2.当日,gz2.当日率,gz2.当日+ mx1.工作累计车日,gz2.当日/(gz2.当日+ mx1.工作累计车日) ,yjh.周转量月计划,zzl.日完成,zzl.日完成+mx1.周转量累计完成,zzl.日完成/(zzl.日完成+mx1.周转量累计完成) ,yjh.吊车月计划,dczzl.日完成,dczzl.日完成+mx1.吊车累计完成,dczzl.日完成/(dczzl.日完成+mx1.吊车累计完成) ,fwh.大修,fwh.中修,fwh.二保,fwh.一保,fwh.小修,fwh.返工,fwh.事故,fwh.出迟,fwh.待料,fwh.待修,fwh.整修,fwh.报废,fwh.小计 ,whts.待任务,whts.待司机,whts.路租,whts.假修,whts.计措,whts.无证,whts.年检,whts.小计 ,fwh.小计+whts.小计 as 停驶合计 ,kgb.当日计划,kgb.当日实际,kgb.当日实际+mx1.卡罐板累计出车 ,dc.当日计划,dc.当日实际,dc.当日实际+mx1.吊车累计出车 from (select CDBH,count(*) as 生产车数,sum(to_number(load))/count(*) as 平均吨位 from VehicleNum where DOCode='1000' group by CDBH) vn left outer join (select DanWei, sum(ZCCSShengChanChe) as 在册累计车数,sum(CLWHLSJCRLeiJiCheRi) as 完好累计车日 ,sum(CLGZLSJCRLeiJiCheRi) as 工作累计车日,sum(ZZLLeiJiWanCheng) as 周转量累计完成 ,sum(DCDXSLeiJiWanCheng) as 吊车累计完成,sum(YTWBYSCLKaGBGCLeiJiChuChe) as 卡罐板累计出车 ,sum(YTWBYSCLDiaoCheLeiJiChuChe) as 吊车累计出车 from SCRB_YSRB_MX where DOCode='1000' and RiQi like substr('20061208',0,6) || '%' and riqi<'20061208' group by DanWei) mx1 on vn.CDBH = mx1.DanWei left outer join /*月计划*/ (select a.CDBH ,sum(y.WanHaoLv) as 完好率月计划,sum(y.GongZuoLv) as 工作率月计划 ,sum(y.ZhouZhuanLiang) as 周转量月计划,sum(y.DunXiaoShi) as 吊车月计划 from YunShuJiHuaBiao y,VehicleNum a where y.DOCode='1000' and y.YueFen=substr('20061208',0,6) and y.CDBH=a.CDBH group by a.CDBH ) yjh on vn.CDBH = yjh.CDBH left outer join /*车辆完好率*/ (select c.CDBH ,count(*) as 当日,count(*)/sum(whl.当日) as 当日率 from VehicleStatusNum s,VehicleNum c ,(select c.CDBH ,count(*) as 当日 from VehicleStatusNum s,VehicleNum c where s.DOCode='1000' and s.Status='13' and s.RiQi='20061208' and s.Num=c.Num group by c.CDBH ) whl where s.DOCode='1000' and s.Status='13' and s.RiQi='20061208' and s.Num=c.Num group by c.CDBH ) wh2 on vn.CDBH = wh2.CDBH /*车辆工作率*/ left outer join (select c.CDBH ,count(*) as 当日,count(*)/sum(gzl.当日) as 当日率 from VehicleStatusNum s,VehicleNum c ,(select c.CDBH ,count(*) as 当日 from VehicleStatusNum s,VehicleNum c where s.DOCode='1000' and s.Status='21' and s.RiQi='20061208' and s.Num=c.Num group by c.CDBH ) gzl where s.DOCode='1000' and s.Status='21' and s.RiQi='20061208' and s.Num=c.Num group by c.CDBH ) gz2 on vn.CDBH = gz2.CDBH left outer join /*周转量*/ (select c.CDBH,sum(SJDGL) as 日完成 from VehicleAttemperNum s,VehicleNum c where s.DOCode='1000' and s.RiQi='20061208' and c.Modul='5' and s.Num=c.Num group by c.CDBH ) zzl on vn.CDBH = zzl.CDBH left outer join /*吊车吨小时*/ (select c.CDBH,sum(SJDGL) as 日完成 from VehicleAttemperNum s,VehicleNum c where s.DOCode='1000' and s.RiQi='20061208' and c.Modul='2' and s.Num=c.Num group by c.CDBH ) dczzl on vn.CDBH =dczzl.CDBH left outer join /*非完好停驶车辆*/ (select c.CDBH ,sum( decode(s.status,'1',1,0) ) as 大修 ,sum( decode(s.status,'2',1,0) ) as 中修 ,sum( decode(s.status,'3',1,0) ) as 二保 ,sum( decode(s.status,'4',1,0) ) as 一保 ,sum( decode(s.status,'5',1,0) ) as 小修 ,sum( decode(s.status,'6',1,0) ) as 返工 ,sum( decode(s.status,'7',1,0) ) as 事故 ,sum( decode(s.status,'8',1,0) ) as 出迟 ,sum( decode(s.status,'9',1,0) ) as 待料 ,sum( decode(s.status,'10',1,0) ) as 待修 ,sum( decode(s.status,'11',1,0) ) as 整修 ,sum( decode(s.status,'12',1,0) ) as 报废 ,sum( decode(s.status,'1',1,0) )+sum( decode(s.status,'2',1,0) ) +sum( decode(s.status,'3',1,0) )+sum( decode(s.status,'4',1,0) ) +sum( decode(s.status,'5',1,0) )+sum( decode(s.status,'6',1,0) ) +sum( decode(s.status,'7',1,0) )+sum( decode(s.status,'8',1,0) ) +sum( decode(s.status,'9',1,0) )+sum( decode(s.status,'10',1,0) ) +sum( decode(s.status,'11',1,0) )+sum( decode(s.status,'12',1,0) ) as 小计 from VehicleStatusNum s,VehicleNum c where s.DOCode='1000' and s.RiQi='20061208' and s.Num=c.Num group by c.CDBH ) fwh on vn.CDBH = fwh.CDBH left outer join /*完好停驶车辆*/ (select c.CDBH ,sum( decode(s.status,'13',1,0) ) as 待任务 ,sum( decode(s.status,'14',1,0) ) as 待司机 ,sum( decode(s.status,'15',1,0) ) as 路租 ,sum( decode(s.status,'16',1,0) ) as 假修 ,sum( decode(s.status,'20',1,0) ) as 计措 ,sum( decode(s.status,'17',1,0) ) as 无证 ,sum( decode(s.status,'19',1,0) ) as 年检 ,sum( decode(s.status,'13',1,0) )+sum( decode(s.status,'14',1,0) ) +sum( decode(s.status,'15',1,0) )+sum( decode(s.status,'16',1,0) ) +sum( decode(s.status,'20',1,0) )+sum( decode(s.status,'17',1,0) ) +sum( decode(s.status,'19',1,0) ) as 小计 from VehicleStatusNum s,VehicleNum c where s.DOCode='1000' and s.RiQi='20061208' and s.Num=c.Num group by c.CDBH ) whts on vn.CDBH = whts.CDBH left outer join /*油田外部运输车辆-卡罐板*/ (select c.CDBH,sum(JHCheRi) as 当日计划,sum(SJCheRi) as 当日实际 from VehicleAttemperNum s,VehicleNum c where s.DOCode='1000' and s.RiQi='20061208' and c.Modul='5' and s.Num=c.Num group by c.CDBH ) kgb on vn.CDBH = kgb.CDBH left outer join /*油田外部运输车辆-吊车*/ (select c.CDBH,sum(JHCheRi) as 当日计划,sum(SJCheRi) as 当日实际 from VehicleAttemperNum s,VehicleNum c where s.DOCode='1000' and s.RiQi='20061208' and c.Modul='2' and s.Num=c.Num group by c.CDBH ) dc on vn.CDBH =dc.CDBH

一条sql 语句，nb！！