多表多表联接查询，求大牛优化

从此不换网名 2018-10-30 10:15:49

多表多表联接查询，求大牛优化，谢谢！





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

...全文

113 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

邹小青 2018-12-26

打赏
举报

可以使用窗口函数，参考《如何协助MySQL实现窗口函数》这篇文章

qq_36802756 2018-11-20

打赏
举报

s'q'l为何写的这么复杂，你想累死他吗

从此不换网名 2018-11-19

打赏
举报

目的：
1.取出当前登录用户的所属角色
2.在通过角色取出所属权限关系
3.在权限列表中取出所有的权限链接
4.判断当前访问的链接是否在所有拥有的权限列表

DreamLLOver 2018-11-08

打赏
举报


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语法上进行优化

DreamLLOver 2018-11-08

打赏
举报

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语法上进行优化

DreamLLOver 2018-11-08

打赏
举报

叶落千尘 2018-11-07

打赏
举报

表的数据量，索引建立，表结构，引擎，主键这些都没有，怎么优化？

从此不换网名 2018-11-01

打赏
举报

能不能把，语句减短又能带到效果，现在执行的效率是0.0016 数据量10万条

王桑的一天 2018-11-01

打赏
举报

你这是让人优化SQL查询语句吗？这怎么优化？这个MySQL引擎就会帮你优化
你的数据库表结构，各个表的数据量、索引全不知道，怎么优化？

薛百珏 2018-10-31

打赏
举报

帮你美化了一下，剩下的力不从心；

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