Oracle SQL-UNION ALL语句的替代方法 [英] Oracle SQL - Alternative to the UNION ALL statement

问题描述

给出使用联合的follownig查询，

Given the follownig query that uses a union,

Select col1,col2,col3,col4,col5,cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)) temp, col6
from tableA
union all
Select a,b,c,d,e,cast(multiset(temp.col1 from table(cast(tableB.children as clob_nt)) temp, col7
from tableB

我希望获得以下输出(即两个表中的行)

i would expect to get the following output (i.e. rows from both tables)

tableA.x tableA.x tableA.x tableA.x tableA.x tableA.clob(x) tableA.x
tableA.x tableA.x tableA.x tableA.x tableA.x tableA.clob(x) tableA.x
tableB.x tableA.x tableA.x tableB.x tableB.x tableB.clob(x) tableB.x
tableB.x tableA.x tableA.x tableB.x tableB.x tableB.clob(x) tableB.x
tableB.x tableA.x tableA.x tableB.x tableB.x tableB.clob(x) tableB.x

还有哪些其他选项可以使我运行查询而无需使用'union'或'union all'并获得相同的结果

What other options are available that will allow me to run the query without using 'union' or 'union all' and get the same results

推荐答案

您实际上可以在Oracle中进行此操作，但是编码有点混乱.想法是在不匹配的字段上执行full outer join，然后使用coalesce()将结果汇总在一起:

You actually can do this in Oracle, but the coding is a bit messy. The idea is to do a full outer join on a non-matching field, and then use coalesce() to bring the results together:

select coalesce(a.col1, b.a) as col1,
       coalesce(a.col2, b.b) as col2,
       coalesce(a.col3, b.c) as col3,
       coalesce(a.col4, b.d) as col4,
       coalesce(a.col5, b.e) as col5,
       coalesce(cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)),
                cast(multiset(temp.col1 from table(cast(b.children as clob_nt))
               ) as temp,
       coalesce(a.col6, b.col7) as col6
from tableA a full outer join
     tableB b
     on 0 = 1;

但是，我不确定上述内容是否可以在temp列上使用.原因之一是cast()似乎没有完整的表述:

However, I'm not sure if the preceding will work on the temp column. One reason is that the cast() doesn't seem to be fully formulated:

这篇关于Oracle SQL-UNION ALL语句的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！