Question

sql多重嵌套子查询，求优化！！！！！

select MenuText from mainmenu where PermissionID in(
select distinct PermissionID from Role_Permission where RoleID
in (select RoleID from User_Roles where roleid
in (select RoleID from User_Roles where userid =
(select id from users where Account='seven'))))

Answer 1

select mm.MenuText from
mainmenu mm,
Role_Permission rp,
user_roles ur,
users u
where mm.PermissionID = rp.PermissionID
and ur.roleid=rp.roleid
and ur.roleid=u.roleid
and Account='seven'

Answer 2

楼主对 user_roles的嵌套好象是重复了一遍，以下sql，请参考
SELECT MenuText
FROM mainmenu mu
WHERE PermissionID EXISTS
(SELECT 1
FROM Role_Permission rp
WHERE mu.PermissionID = rp.PermissionID
AND EXISTS (SELECT 1
FROM User_Roles ur
WHERE rp.RoleID = ur.userid
AND ur.account = 'seven'))

Answer 3

select MenuText from mainmenu where PermissionID in(
select distinct PermissionID from Role_Permission where RoleID
in (select RoleID from User_Roles where userid =
(select id from users where Account='seven')))

Answer 4

应该可以用内连接 inner join。
mainmenu(PermissionID)--Role_Permission (PermissionID )
Role_Permission (RoleID )--User_Roles (roleid )
User_Roles (userid)--users (id )
where users .Account='seven'