mysql - SQL中用where子句实现的表连接怎么用inner join实现?
问题描述
RBAC(Role-Based Access Control,基于角色的访问控制)用5张表实现:
用户表 rbac_user(id, name)角色表 rbac_role(id, name)操作表 rbac_oper(id, name)用户对应的角色表 rbac_relation_user_role(user_id, role_id)角色对应的操作表 rbac_relation_role_oper(role_id, oper_id, value)
建表并插入数据:
CREATE TABLE `rbac_user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;CREATE TABLE `rbac_role` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;CREATE TABLE `rbac_oper` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;CREATE TABLE `rbac_relation_user_role` ( `user_id` int(10) unsigned NOT NULL, `role_id` int(10) unsigned NOT NULL, UNIQUE KEY (`user_id`, `role_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `rbac_relation_role_oper` ( `role_id` int(10) unsigned NOT NULL, `oper_id` int(10) unsigned NOT NULL, `value` int(10) unsigned NOT NULL, UNIQUE KEY (`role_id`, `oper_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO rbac_user VALUES(1,’张三丰’);INSERT INTO rbac_role VALUES(1,’管理员’);INSERT INTO rbac_oper VALUES(1,’foo’);INSERT INTO rbac_oper VALUES(2,’bar’);INSERT INTO rbac_relation_user_role VALUES(1,1);INSERT INTO rbac_relation_role_oper VALUES(1,1,1);INSERT INTO rbac_relation_role_oper VALUES(1,2,0);
用where子句进行表连接获取编号为1的用户具有的操作权限:
select rbac_oper.name, rbac_relation_role_oper.valuefrom rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_operwhere rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.idand rbac_relation_role_oper.role_id = rbac_role.idand rbac_relation_role_oper.oper_id = rbac_oper.idand rbac_user.id = 1结果:name valuefoo 1bar 0
现在的问题是用inner join获得相同的结果该怎么写?我这样写,会提示错误:
select * from rbac_relation_user_role inner join rbac_user on rbac_relation_user_role.user_id = rbac_user.id inner join rbac_role on rbac_relation_user_role.role_id = rbac_role.id inner join rbac_role on rbac_relation_role_oper.role_id = rbac_role.id inner join rbac_oper on rbac_relation_role_oper.oper_id = rbac_oper.idwhere rbac_user.id = 1错误: #1066 - Not unique table/alias: ’rbac_role’
问题解答
回答1:select rbac_oper.name, rbac_relation_role_oper.valuefrom rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_operwhere rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.idand rbac_relation_role_oper.role_id = rbac_role.idand rbac_relation_role_oper.oper_id = rbac_oper.idand rbac_user.id = 1
等价于下面的inner join(缩写join)写法
select rbac_oper.name, rbac_relation_role_oper.value from rbac_relation_user_role join rbac_user on rbac_relation_user_role.user_id = rbac_user.idjoin rbac_role on rbac_relation_user_role.role_id = rbac_role.idjoin rbac_relation_role_oper on rbac_relation_role_oper.role_id = rbac_role.idjoin rbac_oper on rbac_relation_role_oper.oper_id = rbac_oper.idwhere rbac_user.id = 1
注意其中:join rbac_relation_role_oper on rbac_relation_role_oper.role_id = rbac_role.id不能写作:join rbac_role on rbac_relation_role_oper.role_id = rbac_role.id因为要保证所有需要关联的表都被join,同一个表不能join两次.
相关文章:
1. python - Pycharm调试代码进行列表遍历时,如何直接赋值指定元素2. atom编译器 如何格式化前端代码文件?3. python - 基于scrapy-redis的分布式爬虫运行的时候不能正常运行 遇到的问题如下截图所示4. css3 - 我的css用less写的,eclipse的jsp文件要修改css很麻烦,怎么解决?5. android - 新建项目卡在Gradle:Resolvedependencies’:app:_debugCompile’6. javascript - webpack热加载配置不生效7. angular.js - ng-repeat的数据排序,我用了orderBy: d.supportNum为什么失效?8. javascript - 关于在angularjs的select中遇到的问题!!9. javascript - 关于Js中 this的一道题10. 移动端页面文字垂直居中兼容性问题