今天工作用到了这方面的知识,记个笔记,先说基础,把一个表中的部分行插入另一个表,传统写法
insert into
表A
(字段1,字段2,字段3......)
select
(字段1,字段2,字段3......)
from
表B
where 查询条件
我在这里用的类似Map的参数进行传值。并且带入了部分表B中不存在的字段(pur_id,garage_id,create_name等,这些值一定要在插入表A后必须一致,否则就多在表B加一个字段表示不同的东西吧),下面是SQL语句
<!-- 新增采购单详情-->
<insert id="savePurchaseDetailByTemp" parameterType="pd">
insert into
busi_purchase_order_details
(
pur_id,
parts_id,
pur_order_no,
pur_price,
pur_number,
garage_id,
create_name,
create_datetime
)
select
#{pur_id} as pur_id,
b.parts_id as parts_id,
#{pur_order_no} as pur_order_no,
a.pur_price as pur_price,
a.pur_number as pur_number,
#{garage_id} as garage_id,
#{create_name} as create_name,
#{create_datetime} as create_datetime
from
busi_purchase_temp as a,busi_repairparts_info as b
where
a.parts_name=b.parts_name
and
a.i_code=#{i_code}
</insert>