oracle查询中的sql条件联合

kx7yvsdv 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(321)

我有一个查询，它给了我两列，

select

    name as name,
    code as code

from
    table1

UNION

select

    name as name,
    code as code

from
    table2

如果从上面的查询中我没有得到一行，我想在结果中再应用一个并集 name as 'Default' . 因此，如果上面的查询没有具有默认名称的记录，我需要与上面的查询再进行1个并集：

select
        'Default' as name,
        code as code
from
    table1
where condition = condition

我尝试将第一个查询放在视图中，并在第二个查询中使用NotExists函数，但它给出了列NotFound错误。

sql union oracle

来源：https://stackoverflow.com/questions/62869221/conditional-union-in-oracle-query

2条答案

按热度按时间

6tr1vspr1#

我就是这样理解这个问题的：1-9行代表样本数据；其中一行包含 Default 名称，因此结果查询应返回 union -按原样编辑结果：

SQL> with
  2  table1 (name, code) as
  3    (select 'Little', 1 from dual union all
  4     select 'Foot'  , 2 from dual
  5    ),
  6  table2 (name, code) as
  7    (select 'Default', 3 from dual union all   --> Default is here
  8     select 'Oracle' , 4 from dual
  9    ),
 10  -- the "original" union
 11  oriun as
 12    (select name, code from table1
 13     union
 14     select name, code from table2
 15    )
 16  select name, code from oriun
 17  union
 18  select 'Default' name, null code from table1
 19  where not exists (select null from oriun
 20                    where name = 'Default'
 21                   )
 22  order by code;

NAME          CODE
------- ----------
Little           1
Foot             2
Default          3
Oracle           4

SQL>

但是，如果没有 Default 在这些表格中（参见第7行中的更改），您将得到一个“额外的” Default 行：

SQL> with
  2  table1 (name, code) as
  3    (select 'Little', 1 from dual union all
  4     select 'Foot'  , 2 from dual
  5    ),
  6  table2 (name, code) as
  7    (select 'xxx', 3 from dual union all     --> No more Default here
  8     select 'Oracle' , 4 from dual
  9    ),
 10  -- the "original" union
 11  oriun as
 12    (select name, code from table1
 13     union
 14     select name, code from table2
 15    )
 16  select name, code from oriun
 17  union
 18  select 'Default' name, null code from table1
 19  where not exists (select null from oriun
 20                    where name = 'Default'
 21                   )
 22  order by code;

NAME          CODE
------- ----------
Little           1
Foot             2
xxx              3
Oracle           4
Default

SQL>

赞(0）回复(0）举报 2021-07-26

uujelgoq2#

当您有order by in union时，您需要将其放入视图中或使用select*from（。。订购方）

赞(0）回复(0）举报 2021-07-26