SQL中用where子句实现的表连接怎么用inner join实现?

RBACRole-Based Access Control,基于角色的访问控制用5张表实现:

用户表 rbac_userid, name
角色表 rbac_roleid, name
操作表 rbac_operid, name
用户对应的角色表 rbac_relation_user_roleuser_id, role_id
角色对应的操作表 rbac_relation_role_operrole_id, oper_id, value

建表并插入数据:

CREATE TABLE `rbac_user` `id` int10 unsigned NOT NULL AUTO_INCREMENT, `name` varchar255 NOT NULL DEFAULT , PRIMARY KEY `id` ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; CREATE TABLE `rbac_role` `id` int10 unsigned NOT NULL AUTO_INCREMENT, `name` varchar255 NOT NULL DEFAULT , PRIMARY KEY `id` ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; CREATE TABLE `rbac_oper` `id` int10 unsigned NOT NULL AUTO_INCREMENT, `name` varchar255 NOT NULL DEFAULT , PRIMARY KEY `id` ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; CREATE TABLE `rbac_relation_user_role` `user_id` int10 unsigned NOT NULL, `role_id` int10 unsigned NOT NULL, UNIQUE KEY `user_id`, `role_id` ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `rbac_relation_role_oper` `role_id` int10 unsigned NOT NULL, `oper_id` int10 unsigned NOT NULL, `value` int10 unsigned NOT NULL, UNIQUE KEY `role_id`, `oper_id` ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO rbac_user VALUES1,张三丰;
INSERT INTO rbac_role VALUES1,管理员;
INSERT INTO rbac_oper VALUES1,foo;
INSERT INTO rbac_oper VALUES2,bar;
INSERT INTO rbac_relation_user_role VALUES1,1;
INSERT INTO rbac_relation_role_oper VALUES1,1,1;
INSERT INTO rbac_relation_role_oper VALUES1,2,0;

用where子句进行表连接获取编号为1的用户具有的操作权限:

select rbac_oper.name, rbac_relation_role_oper.value
from rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_oper
where rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.id
and rbac_relation_role_oper.role_id = rbac_role.id
and rbac_relation_role_oper.oper_id = rbac_oper.id
and rbac_user.id = 1
结果:
name value
foo 1
bar 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.id
where rbac_user.id = 1
错误: #1066 - Not unique table/alias: rbac_role

select rbac_oper.name, rbac_relation_role_oper.value
from rbac_user, rbac_role, rbac_oper, rbac_relation_user_role, rbac_relation_role_oper
where rbac_relation_user_role.user_id = rbac_user.id and rbac_relation_user_role.role_id = rbac_role.id
and rbac_relation_role_oper.role_id = rbac_role.id
and rbac_relation_role_oper.oper_id = rbac_oper.id
and 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.id
join rbac_role on rbac_relation_user_role.role_id = rbac_role.id
join rbac_relation_role_oper on rbac_relation_role_oper.role_id = rbac_role.id
join rbac_oper on rbac_relation_role_oper.oper_id = rbac_oper.id
where 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两次.

发表评论

电子邮件地址不会被公开。 必填项已用*标注