明细表1字符串拼接合并插入到明细表2SQL输出过程记录

2 years ago

source link: https://blog.51cto.com/mflag/5370683
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

需求描述：

明细表1：formtable_main_46_dt1

字段：id,mainid, bxmx1, jshj, kmbm, fyxm

明细表4：formtable_main_46_dt4

字段：id,mainid,zy,kmbm,jdbmbm,fyxmbm,yfxmbm,jfje

fyxm 为福利费（56）的数据。
bxmx1 拼接，”/”间隔（mysql 结果集行拼接group_concat）。
jshj 取合计（sum）。
插入到明细表4.
同主表，所以mainid相同。

输出过程：

查看明细表1数据。
select mainid, bxmx1, jshj, kmbm, fyxm from formtable_main_46_dt1；
找一个demo数据，mainid=62，以及过滤符合条件的数据，fyxm=56
select mainid, bxmx1, jshj, kmbm, fyxm from formtable_main_46_dt1 where mainid=62 and fyxm =56
尝试字符串合并group_concat
select group_concat(bxmx1) from formtable_main_46_dt1 where mainid=62 and fyxm =56
逗号替换为‘/’
select REPLACE(group_concat(bxmx1),',','/') as zy from formtable_main_46_dt1 where mainid=62 and fyxm =56
select REPLACE(group_concat(bxmx1),',','/') as zy ,sum(jshj) as jfje from formtable_main_46_dt1 where mainid=62 and fyxm =56
添加其它字段
select mainid,REPLACE(group_concat(bxmx1),',','/') as zy ,kmbm,sum(jshj) as jfje from formtable_main_46_dt1 where mainid=62 and fyxm =56
提高兼容性，加group by
select mainid,REPLACE(group_concat(bxmx1),',','/') as zy ,kmbm,sum(jshj) as jfje from formtable_main_46_dt1 where mainid=62 and fyxm =56 group by fyxm
改为insert into 语句前补全字段
select mainid,REPLACE(group_concat(bxmx1),',','/') as zy,kmbm,null as jdbmbm,null as fyxmbm,null as yfxmbm,sum(jshj) as jfje from formtable_main_46_dt1 where mainid=62 and fyxm =56 group by fyxm;
改为insert into 语句（测试需谨慎）
INSERT INTO formtable_main_46_dt4 ( mainid,zy,kmbm,jdbmbm,fyxmbm,yfxmbm,jfje) select mainid,REPLACE(group_concat(bxmx1),',','/') as zy,kmbm,null as jdbmbm,null as fyxmbm,null as yfxmbm,sum(jshj) as jfje from formtable_main_46_dt1 where mainid=62 and fyxm =56 group by fyxm;