MySQL：多表关联查询

select a.id,a.role_name,b.role_1 as ifTrue from sys_role a left join 
(
#用户组角色
select ugrr.permission_policy_id as role_1 from user_group_ref_role ugrr 
where ugrr.group_id in (
  select ugr.group_id from user_group_ref ugr where ugr.user_id = 1115358335685574732 and ugr.is_deleted = 0 ) 
  and ugrr.is_deleted = 0 
  union
#职位角色
select spr.role_id as role_2 from sys_post_role spr 
where spr.post_id in (
  select sup.post_id from sys_user_post sup where sup.user_id = 1115358335685574732 and sup.is_deleted = 0)
and spr.is_deleted = 0 
union
#用户角色
select urr.permission_policy_id as role_3 from user_ref_role urr
where urr.user_id = 1115358335685574732 and urr.is_deleted = 0
#部门角色
union
select sor.role_id as role_4 from sys_org_role sor
where sor.org_id in
(SELECT d3.id
    FROM ( 
        SELECT @r AS id, 
          (SELECT @r := pid FROM sys_organization WHERE id = @r) AS tmp_pid
        FROM (SELECT @r := (select suo.org_id from sys_user_organization suo where suo.user_id = 1115358335685574732)) leafNodeId, 
             sys_organization hd) d2 
    INNER JOIN sys_organization d3 ON d2.id = d3.id AND d2.tmp_pid is not null AND d3.is_deleted = 0
ORDER BY d3.id 
)and sor.is_deleted = 0 ) b on a.id = b.role_1  
where 
a.is_deleted = 0
and a.role_name like concat('%', 'SYS', '%')