api/src/main/resources/sqlScript/院系视图和专业视图.sql

-- 正式环境院系视图
DROP VIEW IF EXISTS bd_dept1;
CREATE VIEW bd_dept1 AS
SELECT t1.id AS pl_dept, t1.code AS deptcode, t1.name AS deptname,'1001' AS pk_crop,
NULL AS deptdirector,t1.mobile AS Tel, NULL AS Tel2
FROM xjr_department t1
WHERE t1.delete_mark = 0 AND t1.enabled_mark = 1;

-- 中台表
CREATE TABLE `test_bd_dept1`(
 `pl_dept` VARCHAR(40) DEFAULT NULL,
 `deptcode` VARCHAR(40) DEFAULT NULL,
 `deptname` VARCHAR(60) DEFAULT NULL,
 `pk_crop` CHAR(4) DEFAULT NULL,
 `deptdirector` VARCHAR(25) DEFAULT NULL,
 `Tel` VARCHAR(30) DEFAULT NULL,
 `Tel2` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIEW';



-- 正式环境专业视图
DROP VIEW IF EXISTS cse_spec1;
CREATE VIEW cse_spec1 AS
SELECT t1.id AS pkspec, t1.code AS speccode, t1.name AS specname,t2.code AS deptcode,'1001' AS pk_crop,
t1.remark AS remarks
FROM base_major_set t1
INNER JOIN xjr_department t2 ON t1.department_id = t2.id
WHERE t1.delete_mark = 0 AND t1.enabled_mark = 1;

-- 中台表结构
CREATE TABLE `test_cse_spec1`(
   `pkspec`  VARCHAR(40) DEFAULT NULL,
 `speccode`  VARCHAR(30) DEFAULT NULL,
 `specname`  VARCHAR(60) DEFAULT NULL,
 `deptcode`  VARCHAR(40) DEFAULT NULL,
 `pk_crop`  CHAR(4) DEFAULT NULL,
 `remarks`  VARCHAR(50) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIEW';