-- 修复员工表中的无效角色代码
|
-- 将无效的角色代码替换为有效的角色代码
|
|
-- 查看当前员工表中的角色代码分布
|
SELECT role_id, COUNT(*) as count FROM t_employee WHERE state = 1 GROUP BY role_id;
|
|
-- 查看有效的角色代码
|
SELECT code, name FROM t_role WHERE state = 1;
|
|
-- 修复无效的角色代码
|
-- MANAGER -> SUPER_ADMIN (管理员)
|
UPDATE t_employee SET role_id = 'SUPER_ADMIN' WHERE role_id = 'MANAGER' AND state = 1;
|
|
-- EMPLOYEE -> AUDITOR (平台工作人员)
|
UPDATE t_employee SET role_id = 'AUDITOR' WHERE role_id = 'EMPLOYEE' AND state = 1;
|
|
-- ADMIN -> SUPER_ADMIN (超级管理员)
|
UPDATE t_employee SET role_id = 'SUPER_ADMIN' WHERE role_id = 'ADMIN' AND state = 1;
|
|
-- 验证修复结果
|
SELECT role_id, COUNT(*) as count FROM t_employee WHERE state = 1 GROUP BY role_id;
|
|
-- 检查是否还有无效的角色代码
|
SELECT e.id, e.name, e.role_id
|
FROM t_employee e
|
LEFT JOIN t_role r ON e.role_id = r.code AND r.state = 1
|
WHERE e.state = 1 AND r.code IS NULL;
|
