多表多表聯接查詢，求大牛優化，謝謝！
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
uj5u.com熱心網友回復：
好亂，看不到執行計劃，估計大牛幫忙能力也有限uj5u.com熱心網友回復：
幫你美化了一下，剩下的力不從心；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
uj5u.com熱心網友回復：
你這是讓人優化SQL查詢陳述句嗎？這怎么優化？這個MySQL引擎就會幫你優化你的資料庫表結構，各個表的資料量、索引全不知道 ，怎么優化？
uj5u.com熱心網友回復：
能不能把，陳述句減短又能帶到效果，現在執行的效率是0.0016 資料量10萬條uj5u.com熱心網友回復：
表的資料量，索引建立，表結構，引擎，主鍵這些都沒有，怎么優化？uj5u.com熱心網友回復：
SELECTrbac_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語法上進行優化
uj5u.com熱心網友回復：
SELECTrbac_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語法上進行優化
uj5u.com熱心網友回復：
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語法上進行優化
uj5u.com熱心網友回復：
目的：1.取出當前登錄用戶的所屬角色
2.在通過角色取出所屬權限關系
3.在權限串列中取出所有的權限鏈接
4.判斷當前訪問的鏈接是否在所有擁有的權限串列
uj5u.com熱心網友回復：
s'q'l為何寫的這么復雜，你想累死他嗎uj5u.com熱心網友回復：
可以使用視窗函式，參考《如何協助MySQL實作視窗函式》這篇文章轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/75218.html
標籤：MySQL
上一篇：如何查詢多個欄位的最大，最小值，并且還要拿到所在的其他欄位
下一篇：求助！兩表查詢求和及求減