mysql 使用查询子句插入数据

清空it_mac.goods it_mac.logistic

 

直接插入到运行中的库中

insert into ithelper.userInfo(phone,departmentId,userName,remark)

 

select distinct phone,departmentId,name asuserName,'综合' as remark

from it_mac.xingzhengas new

 

where not exists(

         select* from ithelper.userInfo

         wherephone = new.phone

);

 

 

增加货物信息goods

其中goodsId categoryId setting mac currentPersonId indexOfBatch需要根据new表中的填

 

goodsId 从goodsCategory查flag，日期为当前日期，批号为Z，货物号为累加

 

 

SET @NUM=0;

SET @GOODSID =date_format( now( ) , '%Y%m%d' );

Insert into it_mac.goods(goodsId,providerId,departmentId,categoryId,setting,location,mac,state,remark,

                            currentPersonId,batchId,indexOfBatch)

 

select

CONCAT(c.flag,@GOODSID, 'Z', right(concat('0000', @NUM+1),3)) asgoodsId,

2 as providerId,

new.departmentId,

new.categoryId,

new.setting,

'IT仓库' as location,

new.mac,

1 as state,

new.remark,

u.userIndex as currentPersonId,

26 as batchId,

@NUM:=@NUM+1 as indexOfBatch

 

from it_mac.xingzhengas new

left join ithelper.userInfo as u on  u.phone = new.phone

left join ithelper.goodsCategoryas c on  c.categoryId =new.categoryId

 

 

 

 

添加logistic记录

Insert into itHelper.logistic(goodsId,userIndex,time,departmentId,state,remark)

Select g.goodsId,u.userIndex,now() as time,u.departmentId,g.state,g.remark 

from it_mac.goodsas g

Left join itHelper.userInfoas u on u.userIndex = g.currentPersonId