CREATE TABLE `org_acc_bu_detail` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`period_name_year` varchar(10) NOT NULL DEFAULT '' COMMENT '年份（会计期）',
`msmt_bu_adjust` varchar(100) NOT NULL DEFAULT '' COMMENT '管报事业部-调整',
`msmt_bu_code_adjust` varchar(30) NOT NULL DEFAULT '' COMMENT '管报事业部-调整code',
`msmt_bu_group` varchar(100) NOT NULL DEFAULT '' COMMENT '管报事业群',
`msmt_account_adjust` varchar(100) NOT NULL DEFAULT '' COMMENT '管报行项目-调整',
`msmt_account_code_adjust` varchar(100) NOT NULL DEFAULT '' COMMENT '管报行项目-调整code',
`amount_total` decimal(19,6) NOT NULL DEFAULT '0.000000' COMMENT '总计',
`tag` varchar(20) NOT NULL DEFAULT '' COMMENT '1:实际数，2:预算数，3:预估预测数据',
`data_type` varchar(20) NOT NULL DEFAULT '' COMMENT '0:除小分摊外其他行项目，1:小分摊',
`dept_code` varchar(30) NOT NULL DEFAULT '' COMMENT '人力架构不为空最底层code'
PRIMARY KEY (`id`),
KEY `idx_region` (`tag`,`data_type`),
KEY `idx_msmt_bu_code_adjust` (`msmt_bu_code_adjust`),
//联合索引
KEY `idx_msmt_account_dept_index` (`msmt_account_code_adjust`,`dept_code`),
//联合唯一索引
UNIQUE KEY `idx_bu_account_tag` (`msmt_bu_code_adjust`,`msmt_account_code_adjust`,`tag`),
KEY `idx_dept_code` (`dept_code`,`msmt_bu_code_adjust`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=274080 DEFAULT CHARSET=utf8mb4 COMMENT='组织费用账单明细表';
select * from dept_auth where is_hrboard = 2 and dept_code_path like concat("100001>100002", '%')
select a.`user_name`, b.`user_name` from `simple_user_arch_type` a left join `simple_user_fee_arch` b on a.`user_name` = b.`user_name`
explain select * from org_acc_bu_detail where period_name_year = '2019' and msmt_account_code_adjust in (select category_code from fee_category where category_parent_code = "EXP02003")
select count(1) from employee_high_performance_info where record_version = '201912' and score in ('A', 'S') and hire_status = '3' and (hire_off_time <= '2019-11-31 23:59:59' and hire_off_time >= '2019-01-01 00:00:00')
alter table learn_employee_project_detail add UNIQUE INDEX idx_user_project (user_name,project_name)
alter table employee_transfer
add `employee_mail` varchar(128) NOT NULL DEFAULT '' COMMENT '邮箱',
add `is_hc` int(11) NOT NULL DEFAULT '0' COMMENT '是否带HC 1是0否',
add `hr_status` int(11) NOT NULL DEFAULT '2' COMMENT '在职状态 2在职(包含预离职) 3已离职',
add `hire_off_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '离职时间',
add `is_water_plan` int(11) NOT NULL DEFAULT '0' COMMENT '是否活水计划 1是0否',
add `process_ticket_code` varchar(128) NOT NULL DEFAULT '' COMMENT '流程单号',
add `dept_level` varchar(50) NOT NULL DEFAULT '' COMMENT '原部门等级',
add `dept_code_path` varchar(256) NOT NULL DEFAULT '' COMMENT '原部门编号路径>间隔',
add `dept_name_path` varchar(256) NOT NULL DEFAULT '' COMMENT '原部门名称路径>间隔',
add `into_dept_name` varchar(50) NOT NULL DEFAULT '' COMMENT '转入部门名称',
add `into_dept_level` varchar(50) NOT NULL DEFAULT '' COMMENT '转入部门等级',
add `into_dept_code_path` varchar(256) NOT NULL DEFAULT '' COMMENT '转入部门编号路径>间隔',
add `into_dept_name_path` varchar(256) NOT NULL DEFAULT '' COMMENT '转入部门名称路径>间隔',
add `into_post_name` varchar(128) NOT NULL DEFAULT '' COMMENT '转入职位名称',
add `into_job_level` varchar(50) NOT NULL DEFAULT '' COMMENT '转入员工职级',
add `into_job_level_number` int(11) NOT NULL DEFAULT '0' COMMENT '转入职级数',
add `into_job_is_d` int(11) NOT NULL DEFAULT '0' COMMENT '转入后职级是否是D，1是，0M',
add `appr_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '审批时间',
add `t6_id` varchar(128) NOT NULL DEFAULT '' COMMENT 't6部门ID',
add `into_t6_id` varchar(50) NOT NULL DEFAULT '' COMMENT '转入部门六级部门ID' after trans_end_date,
modify column into_big_user_field_name varchar(50) NOT NULL DEFAULT '' COMMENT '转入大序列名称',
modify column into_small_user_field_name varchar(50) NOT NULL DEFAULT '' COMMENT '转入小序列名称',
modify column into_sub_user_field_name varchar(50) NOT NULL DEFAULT '' COMMENT '转入子序列名称';