xdmy/doc/sql/u.sql

update pasture p join temDict d on d.factory_name =p.name 
set 
p.company_code=d.company_code,
p.company_name=d.company_name,
p.factory_name=d.factory_name,
p.factory_code=d.factory_code

update warehouse w  join pasture p on p.factory_code =w.factory_code
set w.pastureId=p.id
where p.factory_code  <>""

select  warehosecode,count(*) from warehouse  
group by  warehosecode
having count(*)>1

update provider set enable =0

alter table PartUse modify contractId integer default 0

delete from partuse where id =41772

select id ,r from  (select id ,row_number() over(order by id asc)r from partuse )t where r=17160

select id from partuse where contractId  =''  or contractId is null

update  partuse set contractId2 =contractId
update  partlaid set contractId2 =contractId

delete from Equipment where modifyTime is  not null

update parts set enable =1 ,statue =1

update department d join pasture p on p.id = d.pastureId
set d.pasturename =p.name

update department d join temDict_costcenter t  on d.pasturename =t.factory_name  
and d.name =t.CostCenter_name
set d.CostCenter_code = t.CostCenter_code



update pasture  set factory_code ='M005', parchase_organization ='1012' where name ='察北牧场';
select name from pasture where name like '察北%'

update pasture set factory_code ='M005' ,company_code ='1005',parchase_organization='1005' 
where name ='察北牧场';
update pasture set factory_code 'M005'   where name ='察北牧场';

upadte warehourse  w  join pasture p on p.name = w.factory_name 
set w.pastureId =p.id
where  w.pastureId =0 or w.pastureId is  null

update warehouse set pastureId =115 ,pasture_name ='察北牧场' ,factory_code='M005',
warehoseCode =replace(warehoseCode,char(9),'')
where factory_name ='察北牧场'

update Equipment set pastureId =115 ,companycode='1005'  where companycode ='1006' limit 100

update department d join dept_Data dd on dd.center_name =d.name 
set d.CostCenter_code =dd.CostCenter_code 

update pasture set factory_code ='M001',company_code ='9099' where name ='马鞍山牧场'
update pasture set factory_code ='M008',company_code ='1008' where name ='宝鸡牧场'

update parts set old_partcode =partcode  
update parts set partcode=new_partcode


select  * from part_class where partcode  ='14.01.01'
select  * from part_class where pid = 167


insert into 
part_repertory pr 
left join warehouse w on w.id =pr.locationId 
left join contract c on c.id=pr.contractId 
left join bigcontract bc on bc.id =c.bigId 
left join pasture p on (p.id=pr.pastureId or p.id=w.pastureId )
set reportery =2
where p.name ='塞一牧场'
and bc.contractCode  like 'BJ0220080579%'
and pr.partCode ='14.02.15.06.000046'




update apisql set
sqlstr=
"select (@sort := @sort+1) sort,empname employeeName,DATE_FORMAT(u.createdtime,'%Y-%m-%d') resumeTime,'创建合同' typeName,'提交审核' statusName
 from (SELECT @sort:=0)a,`user` u "
where  sqlname ='getContractResume'

select * from pasture where name ='临沂牧场'
select * from provider  where providerName='无供应商旧件';

select brand from part_repertory  pr where  pr.location ='A-01-01'  and 
(pr.pastureId =629 or (pr.pastureId =18 and  (select pastureId from warehouse where id = pr.`locationId`)= 629) )
395

select partcode ,count(*) from part_repertory  pr where  pr.location ='A-01-01'  and 
(pr.pastureId =618 or (pr.pastureId =18 and  (select pastureId from warehouse where id = pr.`locationId`)= 618) )
group by partcode
having count(*)>1

part_re_temp

update  part_repertory  pr 
join  part_re_temp t   on  pr.partcode =t.code  and pr.brand = t.brand
set pr.reportery  =t.num
where  pr.location ='A-01-01'  and 
(pr.pastureId =618 or (pr.pastureId =18 and  (select pastureId from warehouse where id = pr.`locationId`)= 618) )
and  t.code not in
(
'14.01.01.02.000832',
'14.01.04.01.001202',
'14.01.07.01.002407'
)

330
338
344

select pr.* from part_repertory  pr where  pr.location ='A-01-01'  and 
(pr.pastureId =629 or (pr.pastureId =18 and  (select pastureId from warehouse where id = pr.`locationId`)= 629) )
and partcode in 
(
'14.01.01.02.000832',
'14.01.04.01.001202',
'14.01.07.01.002407'
)

select * from part_re_temp where code  in  
(
'14.01.01.02.000832',
'14.01.04.01.001202',
'14.01.07.01.002407'
)




select pr.partcode,pr.brand,t.num  from part_repertory  pr 
join  part_re_temp t   on  pr.partcode =t.code  and pr.brand = t.brand
where  pr.location ='A-01-01'  and 
(pr.pastureId =629 or (pr.pastureId =18 and  (select pastureId from warehouse where id = pr.`locationId`)= 629) )
and pr.partcode in
(
'14.01.01.02.000832',
'14.01.04.01.001202',
'14.01.07.01.002407'
)


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  =618  or (select pastureId from warehouse w where w.id  =pr.locationid) =618

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 = pr.reportery -12
where pr.pastureid  =618  or (select pastureId from warehouse w where w.id  =pr.locationid) =618

select count(*) 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  =621  or (select pastureId from warehouse w where w.id  =pr.locationid) =621


select * from part_repertory where  partcode ='14.01.04.05.000485'

select * from pasture where name ='云南牧场'
217

alter table partapply  add column created_at datetime