151 lines
6.6 KiB
SQL
151 lines
6.6 KiB
SQL
create table cust_dept_user_grid_temp as
|
||
select d2.parent_id as dept_id #机构Id(支行id)
|
||
, d1.user_id as user_id #用户id
|
||
, d.nick_name as user_name #客户经理名称
|
||
, a.grid_id grid_id #更新一级网格id
|
||
, a.grid_name grid_name #一级网格名称
|
||
, d3.dept_name as dept_name #归属支行名称
|
||
, d.user_name as teller_id #柜员id
|
||
, a.parent_grid_id as grid_id2 #二级网格id
|
||
, b.grid_name as grid_name2 #二级网格名称
|
||
, c.code as code #区域编码
|
||
, d2.dept_id as outlet_id #归属网点id
|
||
, d2.dept_name as outlets_name #归属网点名称
|
||
from grid_region_grid a
|
||
left join grid_region_grid b
|
||
on b.grid_id = a.parent_grid_id
|
||
left join grid_region_admin_division_relate c
|
||
on a.grid_id = c.grid_id
|
||
left join grid_region_user_relate d
|
||
on a.grid_id = d.grid_id
|
||
left join sys_user d1
|
||
on d.user_name = d1.user_name
|
||
left join sys_dept d2
|
||
on d.relate_dept_id = d2.dept_id
|
||
left join sys_dept d3
|
||
on d2.parent_id = d3.dept_id
|
||
where a.grid_level = '2'
|
||
and a.delete_flag = '0';
|
||
|
||
drop table cust_dept_user_grid_orc;
|
||
CREATE TABLE if not exists `cust_dept_user_grid_orc`
|
||
(
|
||
`dept_id` bigint(20) DEFAULT null COMMENT '父部门id(支行)',
|
||
`user_id` bigint(20) DEFAULT null COMMENT '客户经理ID',
|
||
`user_name` varchar(8) DEFAULT null COMMENT '客户经理名称',
|
||
`grid_id` bigint(20) unsigned DEFAULT null COMMENT '一级网格id',
|
||
`grid_name` varchar(90) DEFAULT null COMMENT '一级网格名称',
|
||
`cust_id` varchar(60) DEFAULT null COMMENT '客户号',
|
||
`cust_type` varchar(1) DEFAULT NULL COMMENT '客户类型：0-个人；1-商户；2-企业',
|
||
`cust_name` varchar(90) DEFAULT null COMMENT '客户姓名',
|
||
`cust_idc` varchar(100) DEFAULT null COMMENT '证件号',
|
||
`cust_phone` varchar(100) DEFAULT null COMMENT '手机号',
|
||
`dept_name` varchar(30) DEFAULT null COMMENT '归属支行名称',
|
||
`teller_id` varchar(90) DEFAULT null COMMENT '柜员id',
|
||
`grid_id2` bigint(20) DEFAULT NULL COMMENT '二级网格id',
|
||
`grid_name2` varchar(90) DEFAULT null COMMENT '二级网格名称',
|
||
`code` varchar(255) DEFAULT null COMMENT '区域编码',
|
||
`outlets` varchar(90) DEFAULT null COMMENT '网点名称',
|
||
`outlets_id` varchar(30) DEFAULT null COMMENT '网点ID',
|
||
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE = InnoDB
|
||
AUTO_INCREMENT = 1
|
||
DEFAULT CHARSET = utf8
|
||
;
|
||
|
||
insert into cust_dept_user_grid_orc
|
||
( dept_id
|
||
, user_id
|
||
, user_name
|
||
, grid_id
|
||
, grid_name
|
||
, cust_id
|
||
, cust_type
|
||
, cust_name
|
||
, cust_idc
|
||
, cust_phone
|
||
, dept_name
|
||
, teller_id
|
||
, grid_id2
|
||
, grid_name2
|
||
, code
|
||
, outlets
|
||
, outlets_ID)
|
||
#企业信息表数据插入cust_info_business，cust_type=2
|
||
select b.dept_id # '父部门id',
|
||
, b.user_id # '用户ID',
|
||
, b.user_name # '客户经理姓名',
|
||
, b.grid_id # '0' COMMENT '网格id',
|
||
, b.grid_name # '网格名称',
|
||
, a.cust_id as cust_id
|
||
, 2 as cust_type #客户类型2 企业
|
||
, a.cust_name as cust_name
|
||
, a.social_credit_code as cust_idc
|
||
, a.tel as cust_phone
|
||
, b.dept_name # '部门名称',
|
||
, b.teller_id # '客户经理柜员号',
|
||
, b.grid_id2 # '上级网格id',
|
||
, b.grid_name2 # '网格名',
|
||
, b.code # '行政编码',
|
||
, b.outlets_name as outlets # '部门名称',
|
||
, b.outlets as outlets_id # '部门id'
|
||
#,b.outlets_id as outlets_id # '部门id'
|
||
from ibs.cust_info_business a
|
||
left join cust_dept_user_grid_temp b
|
||
on a.region_code = b.code
|
||
where b.dept_id is not null
|
||
or b.user_id is not null
|
||
|
||
union all
|
||
#商户信息表数据插入 cust_info_merchant，cust_type=1
|
||
select b.dept_id # '父部门id',
|
||
, b.user_id # '用户ID',
|
||
, b.user_name # '客户经理姓名',
|
||
, b.grid_id # '0' COMMENT '网格id',
|
||
, b.grid_name # '网格名称',
|
||
, a.cust_id as cust_id
|
||
, 1 as cust_type #客户类型商户 1
|
||
, a.cust_name as cust_name
|
||
, a.social_credit_code as cust_idc
|
||
, a.cust_phone as cust_phone
|
||
, b.dept_name # '部门名称',
|
||
, b.teller_id # '客户经理柜员号',
|
||
, b.grid_id2 # '上级网格id',
|
||
, b.grid_name2 # '网格名',
|
||
, b.code # '行政编码',
|
||
, b.outlets_name as outlets # '部门名称',
|
||
, b.outlets as outlets_id # '部门id'
|
||
#,b.outlets_id as outlets_id # '部门id'
|
||
from ibs.cust_info_merchant a
|
||
left join cust_dept_user_grid_temp b
|
||
on a.region_code = b.code
|
||
where b.dept_id is not null
|
||
or b.user_id is not null
|
||
|
||
union all
|
||
#个人信息表数据插入 ,ibs.cust_info_retail，cust_type=0
|
||
select b.dept_id # '父部门id'
|
||
, b.user_id # '用户ID',
|
||
, b.user_name # '客户经理姓名',
|
||
, b.grid_id # '0' COMMENT '网格id',
|
||
, b.grid_name # '网格名称',
|
||
, a.cust_id as cust_id
|
||
, 0 as cust_type #客户类型商户 0
|
||
, a.cust_name as cust_name
|
||
, a.cust_idc as cust_idc
|
||
, a.cust_phone as cust_phone
|
||
, b.dept_name # '部门名称',
|
||
, b.teller_id # '客户经理柜员号',
|
||
, b.grid_id2 # '上级网格id',
|
||
, b.grid_name2 # '网格名',
|
||
, b.code # '行政编码',
|
||
, b.outlets_name as outlets # '部门名称',
|
||
, b.outlets as outlets_id # '部门id'
|
||
#,b.outlets_id as outlets_id # '部门id'
|
||
from ibs.cust_info_retail a
|
||
left join cust_dept_user_grid_temp b
|
||
on a.region_code = b.code
|
||
where b.dept_id is not null
|
||
or b.user_id is not null
|
||
; |