56,687
社区成员
发帖
与我相关
我的任务
分享
SELECT rbac_powers.*,modules.name AS modulename FROM ( SELECT rbac_powers_id FROM ( SELECT rbac_userpowers.rbac_powers_id FROM rbac_userpowers WHERE user_id = '5b286d5e3d868' UNION SELECT rbac_grouppowers.rbac_powers_id FROM rbac_usergroup LEFT JOIN rbac_grouppowers ON rbac_grouppowers.rbac_group_id = rbac_usergroup.rbac_group_id WHERE rbac_usergroup.user_id = '5b286d5e3d868' UNION SELECT rbac_rolepowers.rbac_powers_id FROM rbac_usergroup LEFT JOIN rbac_grouprole ON rbac_grouprole.rbac_group_id = rbac_usergroup.rbac_group_id LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id = rbac_grouprole.rbac_role_id WHERE rbac_usergroup.user_id = '5b286d5e3d868' UNION SELECT rbac_rolepowers.rbac_powers_id FROM rbac_userrole LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id = rbac_userrole.rbac_role_id WHERE rbac_userrole.user_id = '5b286d5e3d868' ) B WHERE B.rbac_powers_id !='' GROUP BY rbac_powers_id ) A LEFT JOIN rbac_powers ON rbac_powers.id = A.rbac_powers_id LEFT JOIN modules ON modules.id = rbac_powers.modules_id WHERE modules.foldername ='base' AND ( (rbac_powers.name ='base' AND rbac_powers.level =1) OR (rbac_powers.name ='index' AND rbac_powers.level =2) OR (rbac_powers.name ='index' AND rbac_powers.level =3) OR (rbac_powers.name ='test1' AND rbac_powers.level =4 ) OR (rbac_powers.name ='?id=awdw' AND rbac_powers.level =5 ) ) ORDER BY rbac_powers.level ASC
SELECT
rbac_powers.*, modules. NAME AS modulename ---这里你要获取全部字段 rbac_powers.* 所以下面那个也是* 最好改成指定字段
FROM
(
SELECT
rbac_powers_id
FROM
(
SELECT
rbac_userpowers.rbac_powers_id
FROM
rbac_userpowers
WHERE
user_id = '5b286d5e3d868'
UNION
SELECT
rbac_grouppowers.rbac_powers_id
FROM
rbac_usergroup
LEFT JOIN rbac_grouppowers ON rbac_grouppowers.rbac_group_id = rbac_usergroup.rbac_group_id
WHERE
rbac_usergroup.user_id = '5b286d5e3d868'
UNION
SELECT
rbac_rolepowers.rbac_powers_id
FROM
rbac_usergroup
LEFT JOIN rbac_grouprole ON rbac_grouprole.rbac_group_id = rbac_usergroup.rbac_group_id
LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id = rbac_grouprole.rbac_role_id
WHERE
rbac_usergroup.user_id = '5b286d5e3d868'
UNION
SELECT
rbac_rolepowers.rbac_powers_id
FROM
rbac_userrole
LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id = rbac_userrole.rbac_role_id
WHERE
rbac_userrole.user_id = '5b286d5e3d868'
) B
WHERE B.rbac_powers_id != '' ----这里如果你确认 rbac_powers.id 这个不为空的话, 这里就可以去掉 下面第一个left join 就可以改成 inner join
) A
LEFT JOIN (
select * from rbac_powers where rbac_powers. NAME = 'base' AND rbac_powers. LEVEL = 1
UNION
select * from rbac_powers where rbac_powers. NAME = 'index' AND rbac_powers. LEVEL = 2
UNION
select * from rbac_powers where rbac_powers. NAME = 'index' AND rbac_powers. LEVEL = 3
UNION
select * from rbac_powers where rbac_powers. NAME = 'test1' AND rbac_powers. LEVEL = 4
UNION
select * from rbac_powers where rbac_powers. NAME = '?id=awdw' AND rbac_powers. LEVEL = 5
) as rbac_powers ON rbac_powers.id = A.rbac_powers_id
LEFT JOIN modules ON modules.id = rbac_powers.modules_id
WHERE
modules.foldername = 'base'
ORDER BY
rbac_powers. LEVEL ASC
一切优化还是得看想要什么,只发给sql 只能从sql语法上进行优化
SELECT rbac_powers.*, modules.name AS modulename
FROM (
SELECT rbac_powers_id
FROM (
SELECT rbac_userpowers.rbac_powers_id
FROM rbac_userpowers
WHERE user_id = '5b286d5e3d868'
UNION
SELECT rbac_grouppowers.rbac_powers_id
FROM rbac_usergroup
LEFT JOIN rbac_grouppowers ON rbac_grouppowers.rbac_group_id = rbac_usergroup.rbac_group_id
WHERE rbac_usergroup.user_id = '5b286d5e3d868'
UNION
SELECT rbac_rolepowers.rbac_powers_id
FROM rbac_usergroup
LEFT JOIN rbac_grouprole ON rbac_grouprole.rbac_group_id = rbac_usergroup.rbac_group_id
LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id = rbac_grouprole.rbac_role_id
WHERE rbac_usergroup.user_id = '5b286d5e3d868'
UNION
SELECT rbac_rolepowers.rbac_powers_id
FROM rbac_userrole
LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id = rbac_userrole.rbac_role_id
WHERE rbac_userrole.user_id = '5b286d5e3d868'
) B
WHERE B.rbac_powers_id != ''
GROUP BY rbac_powers_id
) A
LEFT JOIN rbac_powers ON rbac_powers.id = A.rbac_powers_id
LEFT JOIN modules ON modules.id = rbac_powers.modules_id
WHERE modules.foldername = 'base'
AND ((rbac_powers.name = 'base'
AND rbac_powers.level = 1)
OR (rbac_powers.name = 'index'
AND rbac_powers.level = 2)
OR (rbac_powers.name = 'index'
AND rbac_powers.level = 3)
OR (rbac_powers.name = 'test1'
AND rbac_powers.level = 4)
OR (rbac_powers.name = '?id=awdw'
AND rbac_powers.level = 5))
ORDER BY rbac_powers.level ASC