SQL脚本开发经验

需求1： 将Excel中的一个条目的数据关联数据库表中固定条目并将关键字段进行替换

• 生成临时表 aa
  
• 关联两张表并将结果数据插入到新表

INSERT INTO BGT_apptasks_vals2023_819(dpt_code,round_no,task_no,fp_code,sheet_code,sheet_line,line_prjcode,line_prjname,val01,val02,val03,val04,val05,val06,val07,val08,val09,val10,val11,val12,val13,val14,val15,val16,val17,val18,val19,val20,val_ts,tval01,tval02,tval03,tval04,tval05,tval06,tval07,tval08,tval09,tval10,tval11,tval12,tval13,tval14,tval15,tval16,tval17,tval18,tval19,tval20,src_fpcode,src_fpname,src_dptcode,src_dptname,is_newline,tpl_sheetcode)
SELECT a.xx, b.xx, b.xx, b.xx, ......
FROM a, b
WHERE a.fp_code = b.fp_code;

• 关联更新表中某个字段【SQL Server】

UPDATE  t1
SET t1.val04 = t2.amt, t1.tval04 = t2.amt
FROM BGT_apptasks_vals2023_819 t1
LEFT JOIN
(
SELECT b.amt,a.fp_code, a.dpt_code, a.line_prjcode
FROM BGT_apptasks_vals2023_819 a 
left join aa b on a.fp_code = b.fp_code and a.dpt_code = b.dpt_code  and a.line_prjcode = b.code  
where b.amt  <> ''
) t2
ON t1.fp_code = t2.fp_code AND t1.dpt_code = t2.dpt_code AND t1.line_prjcode = t2.line_prjcode;