mysql查询结果插原表_新建表需要原表的数据，mysql 如何把查询到的结果插入到新表中...

项目运用情景：新建表需要原表的数据

1. 如果两张张表(导出表和目标表)的字段一致，并且希望插入全部数据，可以用这种方法：

INSERT INTO  目标表  SELECT  * FROM  来源表 ;

例如，要将 articles 表插入到 newArticles 表中，则可以通过如下SQL语句实现：

INSERT INTO  newArticles  SELECT  * FROM  articles ;

2. 如果只希望导入指定字段，可以用这种方法：

INSERT INTO  目标表 (字段1, 字段2, ...)  SELECT   字段1, 字段2, ...   FROM  来源表 ;  注意后面是没有括号的

请注意以上两表的字段必须一致，否则会出现数据转换错误。

3.项目运用  ： A为新建档案表， B为原有档案表主表

INSERT INTO A(

pk_sr_main,

xzqh,

mhz,

mhzsfz

) SELECT

pk_sr_main,

xzqh,

mhz,

mhzsfz

FROM

B

WHERE

mhzsfz = '332627196510082957';

------------------------------------------------------------------

INSERT INTO A (

sys_spzt,

sys_djzt,

sys_scbj,

mdjlx,

pk_sr_main

) SELECT

(

CASE

WHEN bzzt = '在保' THEN

1

ELSE

4

END

) AS sys_spzt,

(

CASE

WHEN bzzt = '在保' THEN

1

ELSE

0

END

) AS sys_djzt,

0 AS sys_scbj,

'aa' AS mdjlx,

? AS pk_sr_main

FROM

B

WHERE

pk_sr_main = ? ";

---------------------------------------------------------

INSERT INTO 新建表(

pk_sr_main,

fk_sr_zjff_main,

sys_xzqh,

xzqh,

mhz,

mhzsfz,

BTFFJE

) SELECT

nextval ('id'),

'e1a20dc7-b3e8-4cff-8451-aa372a57300d',

a.sys_xzqh,

a.xzqh,

a.mhz,

a.mhzsfz,

CASE

WHEN b.myhzh IS NOT NULL THEN

b.mkhyh

ELSE

a.mkhyh

END AS mkhyh,

CASE

WHEN b.myhzh IS NOT NULL THEN

b.mkhr

ELSE

a.mkhr

END AS mkhr,

CASE

WHEN b.myhzh IS NOT NULL THEN

b.myhzh

ELSE

a.myhzh

END AS myhzh,

CASE

WHEN b.mjjzbh IS NOT NULL THEN

b.mjjzbh

ELSE

a.mjjzbh

END AS mjjzbh,

'0.0'

FROM

原有表A a

LEFT JOIN 原有表A b ON (

b.sys_xzqh LIKE concat('330101', '%')

AND b.sys_spzt = 1

AND b.sys_djzt = 1

AND b.mdjlx = 'mj_jjzg'

AND b.sys_scbj = 0

AND b.sjbfnf = '2020'

AND b.sjbfyf = '02'

AND a.sjbfyzj = b.sjbfyzj

)

WHERE

a.sys_xzqh LIKE concat('330101', '%')

AND a.sys_spzt = 1

AND a.sys_djzt = 1

AND a.mdjlx = 'mj_jjzg'

AND a.sys_scbj = 0

AND a.sjbfnf = '2020'

AND a.sjbfyf = '02'