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

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