INSERT INTO `obpm2`.`tenant_users`(`id`, `authenticated_status`, `authenticated_result`, `authenticated_time`, `birthdate`, `checked_status`, `created_time`, `email`, `habit_setting`, `head_photo`, `im_identity`, `lon_lat_json`, `name`, `sex`, `stature`, `telephone`, `user_type`, `selected_credential_id`, `household_type`, `one_inch_color_white_photo`, `two_inch_color_blue_photo`, `education`, `marital_status`, `military_status`, `nation`, `native_place`, `politics_status`) select tu.id, (case when tu.FIELD21='已认证' then 3 when tu.FIELD23='未认证' then 0 else 2 end), tu.FIELD23, tu.LASTMODIFYTIME, null,(case when tu.FIELD13 ='已核验' then 1 when tu.FIELD13='未提交' then 0 else 2 end), IFNULL(tu.LASTMODIFYTIME,CURRENT_TIMESTAMP),tu.email,null as habit_setting, tu.FIELD20 as head_photo, null as im_identity, tu.FIELD25 as lon_lat_json,tu.`NAME`,0, null as stature, tu.LOGINNO as telephone, 0 as userType, null as selected_credential_id,null as household_type, null as one_inch_color_white_photo,null as two_inch_color_blue_photo, null as education,null as marital_status,null as military_status, null as nation, null as native_place,null as politics_status from t_user tu join identity_user iu on tu.ID=iu.id where iu.id not in (select id from tenant_users) and iu.telephone not in (select telephone from tenant_users); /** 手动删除一些测试的账号信息，比如: 测试环境的如下信息是我们再验证的时候产生的tenant_user select * from tenant_users where telephone in ('15959298383','15959298385','15959394811'); 处理之后，再执行一遍 */ INSERT INTO `obpm2`.`tenant_users`(`id`, `authenticated_status`, `authenticated_result`, `authenticated_time`, `birthdate`, `checked_status`, `created_time`, `email`, `habit_setting`, `head_photo`, `im_identity`, `lon_lat_json`, `name`, `sex`, `stature`, `telephone`, `user_type`, `selected_credential_id`, `household_type`, `one_inch_color_white_photo`, `two_inch_color_blue_photo`, `education`, `marital_status`, `military_status`, `nation`, `native_place`, `politics_status`) select tu.id, (case when tu.FIELD21='已认证' then 3 when tu.FIELD23='未认证' then 0 else 2 end), tu.FIELD23, tu.LASTMODIFYTIME, null,(case when tu.FIELD13 ='已核验' then 1 when tu.FIELD13='未提交' then 0 else 2 end), IFNULL(tu.LASTMODIFYTIME,CURRENT_TIMESTAMP),tu.email,null as habit_setting, tu.FIELD20 as head_photo, null as im_identity, tu.FIELD25 as lon_lat_json,tu.`NAME`,0, null as stature, tu.LOGINNO as telephone, 0 as userType, null as selected_credential_id,null as household_type, null as one_inch_color_white_photo,null as two_inch_color_blue_photo, null as education,null as marital_status,null as military_status, null as nation, null as native_place,null as politics_status from t_user tu join identity_user iu on tu.ID=iu.id where iu.id not in (select id from tenant_users) and iu.telephone not in (select telephone from tenant_users) /** 租户组织信息 其中, 假设正式站点不会出现重复公司名称的情况, 因此, 先忽略这些重复的问题 */ INSERT INTO `tenant_organizations`(`id`, `approved_information_last_updated_time`, `approved_information_note`, `approved_information_status`, `code`, `created_time`, `industry_code`, `institutional_code`, `last_updated_time`, `logo_path`, `name`, `place_of_business_address`, `place_of_business_province_code`, `place_of_business_province_name`, `place_of_business_city_code`, `place_of_business_city_name`, `place_of_business_district_code`, `place_of_business_district_name`, `place_of_register_address`, `place_of_register_province_code`, `place_of_register_province_name`,`place_of_register_city_code`, `place_of_register_city_name`, `place_of_register_district_code`, `place_of_register_district_name`, `tenant_user_id`) select id,'2021-01-01','',2,id,'2021-01-01',field14,field15,'2021-01-01', k.LOGO_URL,k.`NAME`, k.FIELD13 as place_of_business_address, SUBSTRING_INDEX(FIELD13,',',1) as place_of_business_province_code, SUBSTRING_INDEX(FIELD13,',',1) as place_of_business_province_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1) as place_of_business_city_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1) as place_of_business_city_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) as place_of_business_district_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1) as place_of_business_district_name, k.FIELD13 as place_of_register_address, SUBSTRING_INDEX(FIELD12,',',1) as place_of_register_province_code, SUBSTRING_INDEX(FIELD12,',',1) as place_of_register_province_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) as place_of_register_city_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) as place_of_register_city_name, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) as place_of_register_district_code, SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1) as place_of_register_district_name, (select t2.id from t_user t2 where t2.DOMAINID=k.id and IFNULL(t2.ISDOMAINUSER,false)='true' limit 1) from t_domain k where k.`NAME`<>'all_users_zone' and k.id not in (select id from tenant_organizations) and not exists(select * from (select `NAME`,count(1) as ct from t_domain group by `NAME`) as x where ct>1) /** 公司信息 */ INSERT INTO `obpm2`.`tenant_companies`(`id`, `unify_social_credit_code`, `unify_social_credit_code_file`) select a.id,b.FIELD11,b.FIELD16 from tenant_organizations a join t_domain b on a.id=b.ID where a.id not in (select id from tenant_companies); update tenant_companies x, t_domain j set x.unify_social_credit_code=j.FIELD11,x.unify_social_credit_code_file=j.FIELD16 where x.id=j.ID; /** 修改部门 */ INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.INDEXCODE,IFNULL(t.LASTMODIFYTIME,'2021-01-01'),t.LEVELS,t.`NAME`, null as permission_config,0,1,0,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is null and t.ID not in (select ID from obpm2.tenant_departments); /** 需要循环执行几次, 类似如下操作进行修改 update t_department set `NAME`=CONCAT(`NAME`,'..') where id='yJWAZLGc'; select * from ( select count(1) as ct, `NAME`, DOMAIN_ID from t_department group by `NAME`,DOMAIN_ID ) t where t.ct>1; */ INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=1 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=2 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=3 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=4 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=5 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=6 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=7 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=8 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=9 and SUPERIOR in (select id from tenant_departments); INSERT INTO `obpm2`.`tenant_departments` ( `id`, `code`, `created_time`, `display_order`, `index_tree`, `last_updated_time`, `level`, `name`, `permission_config`, `permission_type`, `status`, `total_member`, `organization_id`, `parent_id`) select id,`CODE`,LASTMODIFYTIME,LEVELS,INDEXCODE,LASTMODIFYTIME,LEVELS,`NAME`,permission_config,0,1,0,DOMAIN_ID,SUPERIOR from ( select (length(INDEXCODE)-LENGTH(REPLACE(INDEXCODE,'-',''))) as icount, t.id,t.`CODE`,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as LASTMODIFYTIME,t.INDEXCODE,t.LEVELS,t.`NAME`, null as permission_config,t.DOMAIN_ID,t.SUPERIOR from t_department t join tenant_organizations o on t.DOMAIN_ID=o.id where t.SUPERIOR is not null and t.ID not in (select ID from obpm2.tenant_departments) ) as k where icount=10 and SUPERIOR in (select id from tenant_departments); /** 职员 先手动修改错误的部门信息 */ update t_user h set h.DEFAULTDEPARTMENT=(select r.DEPARTMENTID from t_user_department_role_set r where r.USERID=h.ID limit 1); update tenant_employees c set tenant_user_id=(select b.id from tenant_users b join t_user x on b.telephone=x.LOGINNO where x.id=c.id limit 1); /** 设置部门管理员 */ INSERT INTO `obpm2`.`tenant_department_admins`(`id`, `created_time`, `department_id`, `employee_id`, `organization_id`) select id,'2021-01-01',DEPARTMENTID,USERID,(select u.DOMAINID from t_user u where ID=t.USERID limit 1) from t_user_department_admin_set t where t.ID not in (select l.id from tenant_department_admins l) and t.USERID is not null and USERID in (select id from tenant_employees); /** 默认授权智能人事的应用 */ update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']',',"__xkeybkGaZa1SboC9GL1"]') where BIND_APPLICATIONS not like '%__xkeybkGaZa1SboC9GL1%' and LENGTH(BIND_APPLICATIONS)>5; update t_domain set BIND_APPLICATIONS=REPLACE(BIND_APPLICATIONS,']','"__xkeybkGaZa1SboC9GL1"]') where BIND_APPLICATIONS not like '%__xkeybkGaZa1SboC9GL1%' and LENGTH(BIND_APPLICATIONS)>1; update t_domain set BIND_APPLICATIONS='["__xkeybkGaZa1SboC9GL1"]' where BIND_APPLICATIONS is null; /** 证件信息 */ INSERT INTO `obpm2`.`tenant_user_credentials`(`id`, `created_time`, `address`, `credential_type`, `front_photo`, `number`, `reverse_photo`, `valid_date_from`, `valid_date_to`, `tenant_user_id`, `head_photo`, `name`, `selected`) select mId,mct,address,credential_type,front_photo,number, reverse_photo,valid_date_from,valid_date_to,mtenant_user_id,head_photo,`NAME`, selected from ( select min(x.id) as mId,min(x.created_time) as mct,MAX(x.address) AS address,x.credential_type,MAX(x.front_photo) AS front_photo,x.number, MAX(x.reverse_photo) AS reverse_photo, MAX(x.valid_date_from) AS valid_date_from, MAX(x.valid_date_to) AS valid_date_to,min(x.tenant_user_id) as mtenant_user_id, MAX(x.head_photo) AS head_photo,max(x.`NAME`) as `NAME`,x.selected from ( select t.id,IFNULL(t.LASTMODIFYTIME,'2021-01-01') as created_time, IFNULL(t.FIELD14,'') as address, (case t.FIELD11 when '居民身份证（户口簿）' then 0 when '居民身份证' then 0 when '警号' then 6 when '香港特区护照/身份证' then 1 when '香港特区护照/身份证明' then 1 when '澳门特区护照/身份证' then 2 when '澳门特区护照/身份证明' then 2 when '台湾居民来往大陆通行证' then 3 when '外国护照' then 4 when '退伍军人证' then 5 else -1 end)as credential_type, IFNULL(t.FIELD18,'') as front_photo,IFNULL(t.FIELD12,'')as number, IFNULL(t.FIELD19,'') as reverse_photo, IFNULL(t.FIELD16,'') as valid_date_from,IFNULL(t.FIELD17,'') as valid_date_to, (select k.id from tenant_users k where k.telephone=t.loginno limit 1) as tenant_user_id, IFNULL(t.FIELD15,'') as head_photo, t.`NAME` ,true as selected from t_user t where t.FIELD11 is not null ) as x where x.id not in (select id from tenant_user_credentials) and x.credential_type>-1 and IFNULL(x.number,'')<>'' and number not in (select number from tenant_user_credentials) group by credential_type,number ) as hg ; UPDATE tenant_users x set selected_credential_id=(select k.id from tenant_user_credentials k where k.tenant_user_id=x.id limit 1); update tenant_user_credentials set valid_date_from='1900-01-01' where valid_date_from='0000-00-00'; update tenant_user_credentials set valid_date_to='3050-01-01' where valid_date_to='0000-00-00'; /** 默认授权智能人事 */ /* insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID) select id, (select k.DEPARTMENTID from t_user_department_role_set k where k.USERID=u.ID limit 1) as DepartId, id as userId,'__2uz0l9F3qiXuMQZmma0' from t_user u where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%') */ and ID not in (select IFNULL(USERID,'xxx') from t_user_department_role_set where ROLEID='__2uz0l9F3qiXuMQZmma0'); /** 授权有问题的数据-保证组织管理员拥有智能人事的应用 */ insert into t_user_department_role_set(id,DEPARTMENTID,USERID,ROLEID) select id, (select k.DEPARTMENTID from t_user_department_role_set k where k.USERID=u.ID limit 1) as DepartId,id as userId,'__2uz0l9F3qiXuMQZmma0' from t_user u where u.DOMAINID in (select id from t_domain d where d.BIND_APPLICATIONS like '%__xkeybkGaZa1SboC9GL1%' ) and u.ID not in ( select USERID from t_user_department_role_set) and (u.ISDOMAINUSER='true' or u.ISDOMAINUSER='1'); update tenant_users x set cid=(select k.FIELD24 from t_user k where k.id=x.id limit 1), wechat=(select k.field22 from t_user k where k.id=x.id limit 1); update tenant_companies x, t_domain j set x.unify_social_credit_code=j.FIELD11,x.unify_social_credit_code_file=j.FIELD16 where x.id=j.ID; update tenant_organizations k, t_domain j set lon_lat_json =j.field17 where k.id=j.ID and x.lon_lat_json is null; update tenant_organizations x,t_domain k set x.place_of_business_address=k.FIELD13, x.place_of_business_province_code=SUBSTRING_INDEX(FIELD13,',',1), x.place_of_business_province_name= SUBSTRING_INDEX(FIELD13,',',1), x.place_of_business_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), x.place_of_business_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',2),',',-1), x.place_of_business_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), x.place_of_business_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD13,',',3),',',-1), x.place_of_register_address = k.FIELD13, x.place_of_register_province_code = SUBSTRING_INDEX(FIELD12,',',1), x.place_of_register_province_name = SUBSTRING_INDEX(FIELD12,',',1), x.place_of_register_city_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1) , x.place_of_register_city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',2),',',-1), x.place_of_register_district_code = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), x.place_of_register_district_name = SUBSTRING_INDEX(SUBSTRING_INDEX(FIELD12,',',3),',',-1), x.industry_code=k.FIELD14, x.institutional_code=k.FIELD15 where x.id=k.ID and x.place_of_business_address is null; /** 区域信息 */ update tenant_organizations k set place_of_business_province_code=(select x.id from sys_region x where x.region_full_name=k.place_of_business_province_name limit 1); update tenant_organizations k set place_of_business_city_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name) limit 1); update tenant_organizations k set place_of_business_district_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_business_province_name,k.place_of_register_city_name,k.place_of_business_district_name) limit 1); update tenant_organizations k set place_of_register_province_code=(select x.id from sys_region x where x.region_full_name=k.place_of_register_province_name limit 1); update tenant_organizations k set place_of_register_city_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name) limit 1); update tenant_organizations k set place_of_register_district_code=(select x.id from sys_region x where x.region_full_name=concat(k.place_of_register_province_name,k.place_of_register_city_name,k.place_of_register_district_name) limit 1);