时间戳

首页 > 解决方案 > Combining three select statements to provide single output

sql - Combining three select statements to provide single output

问题描述

I'm trying to pull out a report for DB Roles in Oracle Database which needs to select "DB Name", "Hostname", Grantee, Granted_role. For this purpose three selects were prepared:

select name from v$database;

select host_name from v$instance;

select distinct(grantee), granted_role from dba_role_privs order by 1;

Question is is it possible to combine these and generate a single reporting table:

Server-Name;DB name;Role;Userid

Could anybody please give any hints? Thanks!

标签： sqloracle

解决方案

您可以使用联接来执行此操作，但标量子查询缓存意味着在选择列表中使用标量子查询可能会更快，因为它们会被查询一次，然后值会被所有行重用，例如：

SELECT DISTINCT (SELECT NAME
             FROM   v$database) db_name,
            (SELECT host_name
             FROM   v$instance) host_name,
            grantee,
            granted_role
FROM   dba_role_privs
ORDER  BY 1;

推荐阅读