SQL insert into select 语句
遇到权限数据变更的需要批量到别的平台, 在175平台添加一个权限需要, 批量到别的现有平台, 以后的建站, 会把sql放到自动建站里面;
权限的 insert into select
表一: `ouser`.`u_function` 权限表
表二: misc.gxej_company 平台表
sql:
INSERT INTO `ouser`.`u_function` ( `code`, `parent_code`, `product_type`, `type`, `domain`, `path`, `name`, `desc_`, `level`, `target`, `icon`, `sort_value`, `is_available`, `is_deleted`, `version_no`, `create_userid`, `create_username`, `create_userip`, `create_usermac`, `create_time_db`, `server_ip`, `update_userid`, `update_username`, `update_userip`, `update_usermac`, `update_time_db`, `client_versionno`, `company_id`, `platform_id` ) SELECT '307788', '3077', NULL, '1', '/back-finance-web', '/#/stmMerchantSoStatementListOld/2', '商家销售结算单旧', NULL, '2', NULL, NULL, '99', NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, c.id, '2' FROM misc.gxej_company c where c.id != 175; #去掉175平台
角色的insert into select
涉及到左连接:
insert into ... select ... where ... join语法 例子
insert into T2(c1, c2, c3) select t1.c1, t1.c2, t1.c3 from T1 t1 where t1.c2 = 'y' left join T3 t3 on t1.c1 = t3.c1 left join T4 t4 on t1.c1 = t4.c1;
权限关联表 : u_role_function
角色表: u_role
权限表: u_function
注意: on 条件生产临时表
INSERT INTO `ouser`.`u_role_function` (
`role_id`,
`function_id`,
`is_available`,
`is_deleted`,
`version_no`,
`create_userid`,
`create_username`,
`create_userip`,
`create_usermac`,
`create_time`,
`create_time_db`,
`server_ip`,
`update_userid`,
`update_username`,
`update_userip`,
`update_usermac`,
`update_time_db`,
`client_versionno`,
`company_id`
)
SELECT
r.id,
f.id,
NULL,
'0',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
r.company_id
FROM ouser.u_role r LEFT JOIN ouser.u_function f on r.company_id = f.company_id where r.code ='merchant_role_code_enter_type_1' and f.code LIKE '%307788%'