-- ============================================================ -- 湛江市智慧人社运营运维（2025-2027年）项目 -- 就业一湛通服务平台 - 数据统计 达梦数据库建表脚本 -- -- 依据：《需求规格说明书》第3.11节 - 数据统计 -- 3.11.1 统计岗位归集数量 - 岗位数据回流明细表 -- 3.11.2 农民工返乡返岗统计 - 农民工流动回流明细表 -- 3.11.3 重点人群统计 - 重点人群回流明细表 -- 数据库：达梦数据库 (DM8) -- 创建日期：2025-06-10 -- ============================================================ -- ============================================================ -- 表1：岗位数据回流明细表 -- 说明：按行业、主管部门、县（市、区）、镇街统计岗位归集数量的数据明细 -- 数据来源：省平台回流 -- ============================================================ CREATE TABLE STAT_JOB_REFLUX ( ID VARCHAR(36) NOT NULL, UNIT_CREDIT_CODE VARCHAR(50), ENTERPRISE_NAME VARCHAR(200), DISTRICT VARCHAR(50), JOB_NAME VARCHAR(200), PLAN_HIRE_COUNT INTEGER, JOB_INDUSTRY VARCHAR(100), SALARY_RANGE VARCHAR(100), EDUCATION_REQUIRE VARCHAR(100), JOB_PUBLISH_TIME DATE, JOB_STATUS VARCHAR(50), DATA_REFLUX_TIME DATE, CREATE_BY VARCHAR(50), CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UPDATE_BY VARCHAR(50), UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, SYS_ORG_CODE VARCHAR(50), PRIMARY KEY (ID) ); COMMENT ON TABLE STAT_JOB_REFLUX IS '岗位数据回流明细表'; COMMENT ON COLUMN STAT_JOB_REFLUX.ID IS '主键ID'; COMMENT ON COLUMN STAT_JOB_REFLUX.UNIT_CREDIT_CODE IS '统一社会信用代码'; COMMENT ON COLUMN STAT_JOB_REFLUX.ENTERPRISE_NAME IS '企业名称'; COMMENT ON COLUMN STAT_JOB_REFLUX.DISTRICT IS '所属区县'; COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_NAME IS '招聘岗位名称'; COMMENT ON COLUMN STAT_JOB_REFLUX.PLAN_HIRE_COUNT IS '计划招聘人数'; COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_INDUSTRY IS '岗位所属行业'; COMMENT ON COLUMN STAT_JOB_REFLUX.SALARY_RANGE IS '薪资区间'; COMMENT ON COLUMN STAT_JOB_REFLUX.EDUCATION_REQUIRE IS '学历要求'; COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_PUBLISH_TIME IS '岗位发布时间'; COMMENT ON COLUMN STAT_JOB_REFLUX.JOB_STATUS IS '岗位状态'; COMMENT ON COLUMN STAT_JOB_REFLUX.DATA_REFLUX_TIME IS '数据回流时间'; COMMENT ON COLUMN STAT_JOB_REFLUX.CREATE_BY IS '创建人'; COMMENT ON COLUMN STAT_JOB_REFLUX.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN STAT_JOB_REFLUX.UPDATE_BY IS '修改人'; COMMENT ON COLUMN STAT_JOB_REFLUX.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN STAT_JOB_REFLUX.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_STAT_JOB_REFLUX_UNIT ON STAT_JOB_REFLUX(UNIT_CREDIT_CODE); CREATE INDEX IDX_STAT_JOB_REFLUX_DISTRICT ON STAT_JOB_REFLUX(DISTRICT); CREATE INDEX IDX_STAT_JOB_REFLUX_INDUSTRY ON STAT_JOB_REFLUX(JOB_INDUSTRY); -- ============================================================ -- 表2：农民工流动回流明细表 -- 说明：记录农民工返乡返岗流动的明细数据，支持查询统计导出报表 -- 数据来源：省平台回流 -- ============================================================ CREATE TABLE STAT_WORKER_REFLUX ( ID VARCHAR(36) NOT NULL, ID_CARD VARCHAR(50), FULL_NAME VARCHAR(100), DISTRICT VARCHAR(50), RURAL_REGISTER_FLAG VARCHAR(10), ORIGIN_WORK_AREA VARCHAR(200), WORK_END_DATE DATE, LOCAL_EMPLOY_DATE DATE, PERSON_CATEGORY_TAG VARCHAR(100), DATA_REFLUX_TIME DATE, CREATE_BY VARCHAR(50), CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UPDATE_BY VARCHAR(50), UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, SYS_ORG_CODE VARCHAR(50), PRIMARY KEY (ID) ); COMMENT ON TABLE STAT_WORKER_REFLUX IS '农民工流动回流明细表'; COMMENT ON COLUMN STAT_WORKER_REFLUX.ID IS '主键ID'; COMMENT ON COLUMN STAT_WORKER_REFLUX.ID_CARD IS '身份证号码'; COMMENT ON COLUMN STAT_WORKER_REFLUX.FULL_NAME IS '姓名'; COMMENT ON COLUMN STAT_WORKER_REFLUX.DISTRICT IS '所属区县'; COMMENT ON COLUMN STAT_WORKER_REFLUX.RURAL_REGISTER_FLAG IS '乡村户籍标识'; COMMENT ON COLUMN STAT_WORKER_REFLUX.ORIGIN_WORK_AREA IS '原就业外出地市/省份'; COMMENT ON COLUMN STAT_WORKER_REFLUX.WORK_END_DATE IS '外出就业结束日期'; COMMENT ON COLUMN STAT_WORKER_REFLUX.LOCAL_EMPLOY_DATE IS '本地新就业登记日期'; COMMENT ON COLUMN STAT_WORKER_REFLUX.PERSON_CATEGORY_TAG IS '人员类别标签（脱贫/普通农民工，以回流数据为准）'; COMMENT ON COLUMN STAT_WORKER_REFLUX.DATA_REFLUX_TIME IS '数据回流时间'; COMMENT ON COLUMN STAT_WORKER_REFLUX.CREATE_BY IS '创建人'; COMMENT ON COLUMN STAT_WORKER_REFLUX.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN STAT_WORKER_REFLUX.UPDATE_BY IS '修改人'; COMMENT ON COLUMN STAT_WORKER_REFLUX.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN STAT_WORKER_REFLUX.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_STAT_WORKER_REFLUX_ID_CARD ON STAT_WORKER_REFLUX(ID_CARD); CREATE INDEX IDX_STAT_WORKER_REFLUX_DISTRICT ON STAT_WORKER_REFLUX(DISTRICT); -- ============================================================ -- 表3：重点人群回流明细表 -- 说明：记录离校未就业大学生等重点人群的明细数据，支持查询统计导出报表 -- 数据来源：省平台回流 -- ============================================================ CREATE TABLE STAT_KEY_PERSON_REFLUX ( ID VARCHAR(36) NOT NULL, ID_CARD VARCHAR(50), FULL_NAME VARCHAR(100), DISTRICT VARCHAR(50), KEY_PERSON_CATEGORY VARCHAR(100), CONFIRM_DATE DATE, VALID_DATE DATE, CURRENT_EMPLOY_STATUS VARCHAR(50), DATA_REFLUX_TIME DATE, CREATE_BY VARCHAR(50), CREATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UPDATE_BY VARCHAR(50), UPDATE_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, SYS_ORG_CODE VARCHAR(50), PRIMARY KEY (ID) ); COMMENT ON TABLE STAT_KEY_PERSON_REFLUX IS '重点人群回流明细表'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.ID IS '主键ID'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.ID_CARD IS '身份证号码'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.FULL_NAME IS '姓名'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.DISTRICT IS '所属区县'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.KEY_PERSON_CATEGORY IS '重点人群类别'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CONFIRM_DATE IS '认定日期'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.VALID_DATE IS '有效日期'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CURRENT_EMPLOY_STATUS IS '当前就业状态'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.DATA_REFLUX_TIME IS '数据回流时间'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CREATE_BY IS '创建人'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.CREATE_TIME IS '创建时间'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.UPDATE_BY IS '修改人'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.UPDATE_TIME IS '更新时间'; COMMENT ON COLUMN STAT_KEY_PERSON_REFLUX.SYS_ORG_CODE IS '组织机构编号'; CREATE INDEX IDX_STAT_KEY_PERSON_REFLUX_ID_CARD ON STAT_KEY_PERSON_REFLUX(ID_CARD); CREATE INDEX IDX_STAT_KEY_PERSON_REFLUX_DISTRICT ON STAT_KEY_PERSON_REFLUX(DISTRICT); CREATE INDEX IDX_STAT_KEY_PERSON_REFLUX_CATEGORY ON STAT_KEY_PERSON_REFLUX(KEY_PERSON_CATEGORY);