-- ***************************** -- 创建人员: 杨坤安 -- 创建时间: 2025年4月28日 -- 功能描述: 成本系统数据维护 -- ***************************** DROP TABLE IF EXISTS dm.input_cost_sys_data; CREATE TABLE dm.input_cost_sys_data ( id varchar(200) PRIMARY KEY NOT NULL , create_user varchar(100) ,create_date timestamp ,table1 varchar(200) ,table2 varchar(200) ,table3 varchar(200) ,table4 varchar(200) ,table5 varchar(200) ,table6 varchar(200) ,list_date varchar(100) ,dept_code varchar(100) ,dept_name varchar(200) ,class_code varchar(100) ,class_name varchar(200) ,code varchar(100) ,num bigint ,amt decimal(32,8) ,price decimal(16,8) ,remark1 varchar(200) ,remark2 varchar(200) ,remark3 varchar(200) ,remark4 varchar(200) ,remark5 varchar(200) ,remark6 varchar(200) ,remark7 varchar(200) ,update_date timestamp ,update_by varchar(50) ); -- 为表添加注释 COMMENT ON TABLE dm.input_cost_sys_data IS '成本系统数据维护'; -- 部门 班组 编码 数量 金额 单价 备注1 备注2 备注3 备注4 备注5 备注6 备注7 -- 为字段添加注释 COMMENT ON COLUMN dm.input_cost_sys_data.create_user IS '制单人'; COMMENT ON COLUMN dm.input_cost_sys_data.id IS '主键ID'; COMMENT ON COLUMN dm.input_cost_sys_data.create_date IS '制单日期'; COMMENT ON COLUMN dm.input_cost_sys_data.table1 IS '主表1'; COMMENT ON COLUMN dm.input_cost_sys_data.table2 IS '主表2'; COMMENT ON COLUMN dm.input_cost_sys_data.table3 IS '主表3'; COMMENT ON COLUMN dm.input_cost_sys_data.table4 IS '主表4'; COMMENT ON COLUMN dm.input_cost_sys_data.table5 IS '主表5'; COMMENT ON COLUMN dm.input_cost_sys_data.table6 IS '主表6'; COMMENT ON COLUMN dm.input_cost_sys_data.list_date IS '明细表日期'; COMMENT ON COLUMN dm.input_cost_sys_data.dept_code IS '部门编码'; COMMENT ON COLUMN dm.input_cost_sys_data.dept_name IS '部门名称'; COMMENT ON COLUMN dm.input_cost_sys_data.class_code IS '班组编码'; COMMENT ON COLUMN dm.input_cost_sys_data.class_name IS '班组名称'; COMMENT ON COLUMN dm.input_cost_sys_data.code IS '编码'; COMMENT ON COLUMN dm.input_cost_sys_data.num IS '数量'; COMMENT ON COLUMN dm.input_cost_sys_data.amt IS '金额'; COMMENT ON COLUMN dm.input_cost_sys_data.price IS '单价'; COMMENT ON COLUMN dm.input_cost_sys_data.remark1 IS '备注1'; COMMENT ON COLUMN dm.input_cost_sys_data.remark2 IS '备注2'; COMMENT ON COLUMN dm.input_cost_sys_data.remark3 IS '备注3'; COMMENT ON COLUMN dm.input_cost_sys_data.remark4 IS '备注4'; COMMENT ON COLUMN dm.input_cost_sys_data.remark5 IS '备注5'; COMMENT ON COLUMN dm.input_cost_sys_data.remark6 IS '备注6'; COMMENT ON COLUMN dm.input_cost_sys_data.remark7 IS '备注7'; COMMENT ON COLUMN dm.input_cost_sys_data.update_date IS '更新日期'; COMMENT ON COLUMN dm.input_cost_sys_data.update_by IS '更新人'; DROP TABLE IF EXISTS dm.input_cost_sys_data_list; CREATE TABLE dm.input_cost_sys_data_list ( id varchar(200) PRIMARY KEY NOT NULL ,cost_id varchar(200) ,create_user varchar(100) ,create_date timestamp ,table1 varchar(200) ,table2 varchar(200) ,table3 varchar(200) ,table4 varchar(200) ,table5 varchar(200) ,table6 varchar(200) ,list_date varchar(100) ,dept_code varchar(100) ,dept_name varchar(200) ,class_code varchar(100) ,class_name varchar(200) ,code varchar(100) ,num bigint ,amt decimal(32,8) ,price decimal(16,8) ,remark1 varchar(200) ,remark2 varchar(200) ,remark3 varchar(200) ,remark4 varchar(200) ,remark5 varchar(200) ,remark6 varchar(200) ,remark7 varchar(200) ,update_date timestamp ,update_by varchar(50) ); -- 为表添加注释 COMMENT ON TABLE dm.input_cost_sys_data_list IS '成本系统数据维护'; -- 部门 班组 编码 数量 金额 单价 备注1 备注2 备注3 备注4 备注5 备注6 备注7 -- 为字段添加注释 COMMENT ON COLUMN dm.input_cost_sys_data_list.create_user IS '制单人'; COMMENT ON COLUMN dm.input_cost_sys_data_list.id IS '主键ID'; COMMENT ON COLUMN dm.input_cost_sys_data_list.cost_id IS '表ID'; COMMENT ON COLUMN dm.input_cost_sys_data_list.create_date IS '制单日期'; COMMENT ON COLUMN dm.input_cost_sys_data_list.table1 IS '主表1'; COMMENT ON COLUMN dm.input_cost_sys_data_list.table2 IS '主表2'; COMMENT ON COLUMN dm.input_cost_sys_data_list.table3 IS '主表3'; COMMENT ON COLUMN dm.input_cost_sys_data_list.table4 IS '主表4'; COMMENT ON COLUMN dm.input_cost_sys_data_list.table5 IS '主表5'; COMMENT ON COLUMN dm.input_cost_sys_data_list.table6 IS '主表6'; COMMENT ON COLUMN dm.input_cost_sys_data_list.list_date IS '明细表日期'; COMMENT ON COLUMN dm.input_cost_sys_data_list.dept_code IS '部门编码'; COMMENT ON COLUMN dm.input_cost_sys_data_list.dept_name IS '部门名称'; COMMENT ON COLUMN dm.input_cost_sys_data_list.class_code IS '班组编码'; COMMENT ON COLUMN dm.input_cost_sys_data_list.class_name IS '班组名称'; COMMENT ON COLUMN dm.input_cost_sys_data_list.code IS '编码'; COMMENT ON COLUMN dm.input_cost_sys_data_list.num IS '数量'; COMMENT ON COLUMN dm.input_cost_sys_data_list.amt IS '金额'; COMMENT ON COLUMN dm.input_cost_sys_data_list.price IS '单价'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark1 IS '备注1'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark2 IS '备注2'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark3 IS '备注3'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark4 IS '备注4'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark5 IS '备注5'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark6 IS '备注6'; COMMENT ON COLUMN dm.input_cost_sys_data_list.remark7 IS '备注7'; COMMENT ON COLUMN dm.input_cost_sys_data_list.update_date IS '更新日期'; COMMENT ON COLUMN dm.input_cost_sys_data_list.update_by IS '更新人'; SELECT A.ID ,B.emp_name AS create_user -- 制单人 ,A.create_date AS create_date -- 制单日期 ,A.table1 AS table1 -- 主表1 ,A.table2 AS table2 -- 主表2 ,A.table3 AS table3 -- 主表3 ,A.table4 AS table4 -- 主表4 ,A.table5 AS table5 -- 主表5 ,A.table6 AS table6 -- 主表6 FROM ( SELECT distinct COST_id AS ID ,create_user -- 制单人 ,create_date -- 制单日期 ,table1 -- 主表1 ,table2 -- 主表2 ,table3 -- 主表3 ,table4 -- 主表4 ,table5 -- 主表5 ,table6 -- 主表6 FROM dm.input_cost_sys_data_list WHERE 1=1 ${if(len(sTb1)=0,"","AND table1 IN ('"+sTb1+"') ")} ${if(len(sTb2)=0,"","AND table2 IN ('"+sTb2+"') ")} ${if(len(sTb3)=0,"","AND table3 IN ('"+sTb3+"') ")} )A LEFT JOIN ( SELECT emp_code -- 员工编号 ,emp_name -- 员工名称 from dwr.dim_employee )B ON A.create_user = B.emp_code ORDER BY A.create_date desc,A.table1,A.table2,A.table3 ; INSERT INTO dm.input_cost_sys_data_list ( id ,cost_id ,create_user ,create_date ,table1 ,table2 ,table3 ,table4 ,table5 ,table6 ,list_date ,dept_code ,dept_name ,class_code ,class_name ,code ,num ,amt ,price ,remark1 ,remark2 ,remark3 ,remark4 ,remark5 ,remark6 ,remark7 ,update_date ,update_by) SELECT s.id ,s.cost_id ,s.create_user ,s.create_date ,s.table1 ,s.table2 ,s.table3 ,s.table4 ,s.table5 ,s.table6 ,s.list_date ,s.dept_code ,s.dept_name ,s.class_code ,s.class_name ,s.code ,s.num ,s.amt ,s.price ,s.remark1 ,s.remark2 ,s.remark3 ,s.remark4 ,s.remark5 ,s.remark6 ,s.remark7 ,s.update_date ,s.update_by FROM dm.input_cost_sys_data_list_1 s LEFT JOIN dm.input_cost_sys_data_list t ON s.id = t.id WHERE t.id IS NULL;