mysql 使用查询子句插入数据

清空 it_mac.goods it_mac.logistic

 

直接插入到运行中的库中

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

 

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

from it_mac.xingzheng as new

 

where not exists(

         select * from ithelper.userInfo

         where phone = 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)) as goodsId,

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.xingzheng as new

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

left join ithelper.goodsCategory as 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.goods as g

Left join itHelper.userInfo as u on u.userIndex = g.currentPersonId