mysql-单语句复杂数据查询

此查询将获取单个用户直接拥有或由组给定的每个角色

SELECT * FROM ROLE WHERE ID IN (
    SELECT ROLE_ID
    FROM USER_HAS_ROLE 
    WHERE USER_ID = 1
    UNION
    SELECT ROLE_ID
    FROM USER_HAS_GROUP UG
    INNER JOIN GROUP_HAS_ROLE GR ON UG.GROUP_ID = GR.GROUP_ID
    WHERE USER_ID = 1
)

用途:

   SELECT DISTINCT r.name AS role_name
     FROM USER u
LEFT JOIN USER_HAS_ROLE uhr ON uhr.user_id = u.id 
LEFT JOIN USER_HAS_GROUP uhg ON uhg.user_id = u.id 
LEFT JOIN GROUP_HAS_ROLE ghr ON ghr.group_id = uhg.group_id 
LEFT JOIN ROLE r ON r.id = uhr.role_id
                 OR r.id = ghr.role_id
    WHERE u.username = ?

试试这个:

  Select role_id 
  From user_has_role
  Where userId = @UserId
  Union
  Select role_id 
  From user_has_group g
     Join Group_has_Role gr
        On gr.GroupId = g.GroupId 
  Where userId = @UserId

您可以找到用户的所有不同角色 1 像:

select  distinct role_id
from    (
        select  uhr.user_id
        ,       uhr.role_id
        from    user_has_role uhr
        union all
        select  uhg.user_id
        ,       ghr.role_id
        from    user_has_group uhg
        join    group_has_role ghr
        on      ghr.group_id = uhg.group_id
        where   not exists
                (
                select  *
                from    user_has_role uhr
                where   uhr.user_id = uhg.user_id
                        and uhr.role_id = ghr.role_id
                )
        ) user2role    
where   user_id = 1

不确定与用户相关的角色应该如何“占上风”,但您可以在联合中将优先级分配给他们。