帆软系统-查看拥有某个菜单权限的用户

朱雀 2022-09-04 02:55 228阅读 0赞

# Oracle版： #

WITH a as(  
SELECT  
b.displayName,  
b.deviceType,  
dbms\_lob.substr(b.path) as path,  
c.realName,  
c.userName,  
e.name  
FROM  
FR10SYS.fine\_authority  a  
LEFT JOIN FR10SYS.fine\_authority\_object  b ON a.authorityEntityId = b.id  
RIGHT  JOIN FR10SYS.fine\_user  c ON a.roleId = c.id   
LEFT JOIN  FR10SYS.fine\_user\_role\_middle  d ON c.id = d.userId  
LEFT JOIN FR10SYS.fine\_custom\_role  e on d.roleId=e.id  
WHERE  
a.authorityType = 1 AND  
a.authority = 2  
)  
, b as(  
SELECT  
b.displayName,  
b.deviceType,  
dbms\_lob.substr(b.path) as path,  
e.realName,  
e.userName,  
c.name  
FROM  
FR10SYS.fine\_authority  a  
LEFT JOIN FR10SYS.fine\_authority\_object  b ON a.authorityEntityId = b.id  
LEFT JOIN FR10SYS.fine\_custom\_role  c ON a.roleId = c.id  
LEFT JOIN FR10SYS.fine\_user\_role\_middle  d ON c.id = d.roleId  
right JOIN FR10SYS.fine\_user  e ON d.userId = e.id  
WHERE  
a.authorityType = 1 AND  
a.authority = 2  
)  
,c as  
(SELECT \* FROM a   
UNION   
SELECT \* FROM b)

SELECT \* from c

# SqlServer版： #

WITH a as(
    SELECT
    b.displayName,
    b.deviceType,
    b.path,
    c.realName,
    c.userName,
    e.name
    FROM
    dbo.fine_authority AS a
    LEFT JOIN dbo.fine_authority_object AS b ON a.authorityEntityId = b.id
    RIGHT  JOIN dbo.fine_user AS c ON a.roleId = c.id 
    LEFT JOIN  dbo.fine_user_role_middle AS d ON c.id = d.userId
    LEFT JOIN dbo.fine_custom_role as e on d.roleId=e.id
    WHERE
    a.authorityType = 1 AND
    a.authority = 2
    )
    , b as(
    SELECT
    b.displayName,
    b.deviceType,
    b.path,
    e.realName,
    e.userName,
    c.name
    FROM
    dbo.fine_authority AS a
    LEFT JOIN dbo.fine_authority_object AS b ON a.authorityEntityId = b.id
    LEFT JOIN dbo.fine_custom_role AS c ON a.roleId = c.id
    LEFT JOIN dbo.fine_user_role_middle AS d ON c.id = d.roleId
    right JOIN dbo.fine_user AS e ON d.userId = e.id
    WHERE
    a.authorityType = 1 AND
    a.authority = 2
    )
    ,c as
    (SELECT * FROM a 
    UNION 
    SELECT * FROM b)
    SELECT * from c