Oracle 我可以得到一个表，其中每列由一个查询 (每个查询返回三行与id)？

ASKTOM 2019-10-23

204

问题描述

嗨，

我有27个不同的查询，所有这些查询最多产生3行。像这样的东西:

SELECT CUSTOMER_NUMBER FROM customer WHERE name = 'SomeName' order by CUSTOMER_NUMBER fetch first 3 rows only;
复制

和

SELECT CUSTOMER_NUMBER FROM customer WHERE dateOfBirth < TO_DATE('01.01.2000', 'dd.mm.yyyy') order by CUSTOMER_NUMBER fetch first 3 rows only;
复制

显然，where子句可能要复杂得多，但是所有查询都返回 (最多3个) CUSTOMER_NUMBER行。

如何在一个表中显示所有查询 _ 像这样:

         Customer Number 1     Customer Number 2     Customer Number 3
Query 1               a                     b                     c
Query 2               d                     e                     f
Query 3               g                     h
...
复制

这可能吗？(如果更容易的话，我也会对转置表感到满意)。
请注意，如果我简单地使用 “UNION” 或 “与”，我从查询1中获取三行 _ times _ 查询2中的三行...

非常感谢!
菲利普

专家解答

您可以将所有查询联合在一起，并对结果进行透视:

with rws as (
( 
  select distinct 'Q1' q, owner from dba_tables
  order  by owner
  fetch  first 3 rows only
) union all (
  select distinct 'Q2' q, sequence_owner
  from dba_sequences
  order  by sequence_owner
  fetch  first 3 rows only
)
), rns as (
  select q, owner, row_number () over ( 
           partition by q order by owner 
         ) rn
  from   rws
)
  select * from rns
  pivot (
    min ( owner ) for rn in (
      1, 2, 3
    )
  );

Q    1           2        3        
Q1   APPQOSSYS   AUDSYS   CHRIS     
Q2   CHRIS       CO       CTXSYS  复制