xdmy/doc/sql/i.sql

-- insert into   temDict_costcenter  
--  (CostCenter_code,CostCenter_name,factory_name)
-- values 
-- ('9099010501',			'品控处',    	'马鞍山牧场'),
-- ('9099010101',			'财务处',    	'马鞍山牧场'),
-- ('9099010102',			'信息处',    	'马鞍山牧场'),
-- ('9099010103',			'办公室',    	'马鞍山牧场'),
-- ('9099010502',			'饲养处',    	'马鞍山牧场'),
-- ('9099010301',			'挤奶处',    	'马鞍山牧场'),
-- ('9099010503',			'技术处',    	'马鞍山牧场'),
-- ('9099010504',			'犊牛处',    	'马鞍山牧场'),
-- ('9099010505',			'饲喂处',    	'马鞍山牧场'),
-- ('9099010506',			'设备处',    	'马鞍山牧场'),
-- ('9099010507',			'安环处',    	'马鞍山牧场'),
-- ('9099010104',			'餐厅',    	'马鞍山牧场'),
-- ('9099010508',			'沼液车间',    	'马鞍山牧场'),
-- ('9099010401',			'沼渣车间',    	'马鞍山牧场'),
-- ('9099010509',			'污水车间',    	'马鞍山牧场'),
-- ('9099010402',			'水处理车间',    	'马鞍山牧场'),
-- ('9099010105',			'总裁办',    	'马鞍山牧场')
-- ;

-- insert into warehouse  (factory_name,warehoseCode,warehosetName,enable)
-- values
-- ('察北牧场','1001',	'原材料仓',1),
-- ('察北牧场','1002',	'药品仓',1),
-- ('察北牧场','1003',	'药品二级库',1),
-- ('察北牧场','1004',	'备件仓',1),
-- ('察北牧场','1005',	'综合仓',1),
-- ('察北牧场','1101',	'青贮仓-1号窖',1),
-- ('察北牧场','1102',	'青贮仓-2号窖',1),
-- ('察北牧场','1103',	'青贮仓-3号窖',1),
-- ('察北牧场','1104',	'青贮仓-4号窖',1),
-- ('察北牧场','1105',	'青贮仓-5号窖',1),
-- ('察北牧场','1106',	'青贮仓-6号窖',1),
-- ('察北牧场','1107',	'青贮仓-7号窖',1),
-- ('察北牧场','1108',	'青贮仓-8号窖',1),
-- ('察北牧场','1109',	'青贮仓-9号窖',1),
-- ('察北牧场','1110',	'青贮仓-10号窖',1),
-- ('察北牧场','1111',	'青贮仓-11号窖',1),
-- ('察北牧场','1112',	'青贮仓-12号窖',1),
-- ('察北牧场','1113',	'青贮仓-13号窖',1),
-- ('察北牧场','1114',	'青贮仓-14号窖',1),
-- ('察北牧场','1115',	'青贮仓-15号窖',1),
-- ('察北牧场','1116',	'青贮仓-16号窖',1),
-- ('察北牧场','1117',	'青贮仓-17号窖',1),
-- ('察北牧场','1118',	'青贮仓-18号窖',1),
-- ('察北牧场','1119',	'青贮仓-19号窖',1),
-- ('察北牧场','1120',	'青贮仓-20号窖',1),
-- ('察北牧场','1201',	'原奶仓',1);

-- insert into dept_Data values
-- ('1005170501',	'二期品控处'),		
-- ('1005170101',	'二期财务处'),		
-- ('1005170102',	'二期信息处'),		
-- ('1005170103',	'二期办公室'),		
-- ('1005170502',	'二期饲养处'),		
-- ('1005170301',	'二期挤奶处'),		
-- ('1005170503',	'二期技术处'),		
-- ('1005170504',	'二期犊牛处'),		
-- ('1005170505',	'二期饲喂处'),		
-- ('1005170506',	'二期设备处'),		
-- ('1005170507',	'二期沼液车间'),		
-- ('1005170401',	'二期沼渣车间'),		
-- ('1005170508',	'二期污水车间'),		
-- ('1005170402',	'二期水处理车间')	
-- insert into cost_center_temp values
-- ('1005050501',	'品控处',	'F'),
-- ('1005050101',	'财务处',	'M'),
-- ('1005050102',	'信息处',	'M'),
-- ('1005050103',	'办公室',	'M'),
-- ('1005050502',	'饲养处',	'F'),
-- ('1005050301',	'挤奶处',	'P'),
-- ('1005050503',	'技术处',	'F'),
-- ('1005050504',	'犊牛处',	'F'),
-- ('1005050505',	'饲喂处',	'F'),
-- ('1005050506',	'设备处',	'F'),
-- ('1005050507',	'安环处',	'F'),
-- ('1005050508',	'沼液车间',	'F'),
-- ('1005050401',	'沼渣车间',	'A'),
-- ('1005050509',	'污水车间',	'F'),
-- ('1005050402',	'水处理车间',	'A'),
-- ('1005050104',	'餐厅',	'M'),
-- ('1005170501',	'二期品控处',	'F'),
-- ('1005170101',	'二期财务处',	'M'),
-- ('1005170102',	'二期信息处',	'M'),
-- ('1005170103',	'二期办公室',	'M'),
-- ('1005170502',	'二期饲养处',	'F'),
-- ('1005170301',	'二期挤奶处',	'P'),
-- ('1005170503',	'二期技术处',	'F'),
-- ('1005170504',	'二期犊牛处',	'F'),
-- ('1005170505',	'二期饲喂处',	'F'),
-- ('1005170506',	'二期设备处',	'F'),
-- ('1005170507',	'二期沼液车间',	'F'),
-- ('1005170401',	'二期沼渣车间',	'A'),
-- ('1005170508',	'二期污水车间',	'F'),
-- ('1005170402',	'二期水处理车间',	'A'),
-- ('1005170509',	'二期安环处',	'F')

create temporary table save_temp (
code  varchar(100),
providername  varchar(100),
brand  varchar(100),
price decimal(15,5),
num  decimal(15,5)
)
num  integer 





select  pr.contractid  from 
part_repertory pr 
left join warehouse w on w.id =pr.locationId 
left join pasture p on (p.id=pr.pastureId or p.id=w.pastureId )
where p.name ='衡水二牧'
and pr.partCode ='14.04.02.01.000335'

select * from partlaid where contractid =598726 and locationid =8855

select * from brand 

select * from pasture where name='衡水二牧'

select code from save_temp where code  not in (
select pr.partcode   
from  
part_repertory pr 
join save_temp s on pr.partcode =s.code and pr.price =s.price and pr.brand  =s.brand
join provider pv on pv.id =pr.providerId  and pv.providerName  =s.providername
where pr.pastureid  =619  or (select pastureId from warehouse w where w.id  =pr.locationid) =619
)t
where t.part

update 
part_repertory pr 
join save_temp s on pr.partcode =s.code and pr.price =s.price and pr.brand  =s.brand
join provider pv on pv.id =pr.providerId  and pv.providerName  =s.providername
set pr.reportery = s.num
where pr.pastureid  =619  or (select pastureId from warehouse w where w.id  =pr.locationid) =619