对两个表结果进行分组，没有重复的结果

Question

我正在尝试从特定用户的两个不同表中获取角色和权限。我的查询是这样的：

Select r.grantee, r.granted_role , s.privilege 
From dba_role_privs r, dba_sys_privs s 
Where r.grantee=s.grantee and r.grantee=(select username from dba_users where username='HR'); 

我最终得到这个：

GRANTEE    GRANTED_ROLE         PRIVILEGE
---------- -------------------- --------------------
HR         DBA                  CREATE VIEW
HR         RESOURCE             CREATE VIEW
HR         DBA                  UNLIMITED TABLESPACE
HR         RESOURCE             UNLIMITED TABLESPACE
HR         DBA                  CREATE DATABASE LINK
HR         RESOURCE             CREATE DATABASE LINK
HR         DBA                  CREATE SEQUENCE
HR         RESOURCE             CREATE SEQUENCE
HR         DBA                  CREATE SESSION
HR         RESOURCE             CREATE SESSION
HR         DBA                  ALTER SESSION
GRANTEE    GRANTED_ROLE         PRIVILEGE
---------- -------------------- --------------------
HR         RESOURCE             ALTER SESSION
HR         DBA                  CREATE SYNONYM
HR         RESOURCE             CREATE SYNONYM
14 rows selected.

当用户没有很多角色/权限时，这很好，但是对于像SYS这样的用户，例如，我最终会得到10600 rows selected.

有没有办法使查询结果更好？ 我错过了联接吗？

PS：我正在开发oracle 11g。

如果您可以帮助使其更好，请这样做

提前感谢！

Answer 1

也许你想要union all而不是连接：

Select r.grantee, r.granted_role as role_or_privilege
From dba_role_privs r, 
Where r.grantee = (select username from dba_users where username = 'HR'); 
union all
select s.grantee, s.privilege 
from dba_sys_privs s 
Where s.grantee = (select username from dba_users where username = 'HR'); 

我将where中的逻辑与您的查询相同。 但是，这对我来说更有意义：

Select r.grantee, r.granted_role as role_or_privilege
From dba_role_privs r, 
Where r.grantee = 'HR'; 
union all
select s.grantee, s.privilege 
from dba_sys_privs s 
Where s.grantee = 'HR';