244 lines
9.6 KiB
MySQL
244 lines
9.6 KiB
MySQL
|
-- *****************************
|
||
|
-- 创建人员: 杨坤安
|
||
|
-- 创建时间: 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;
|