Oracle中的工会替代方案 [英] Alternative for union in Oracle

问题描述

是否有其他方法可以在不使用联合的情况下获取以下数据?

Is there any alternate way to fetch the following data without using union?

select A.name,A.age,B.Address,C.phoneNo from table1 A,Table2 B,Table3 C where a.pkId = b.FkId and b.pkId = c.FkId
union
select A.name,A.age,B.Address,C.phoneNo from table4 A,Table5 B,Table3 C where a.pkId = b.FkId and b.pkId = c.FkId

我在Hibernate中使用了它，不幸的是hibernate不支持Union.我只是想知道是否还有其他方法可以实现它，否则就不得不在过程中编写它并将数据保存在临时表中并触发sql从该临时表中读取数据

I am using this in Hibernate and unfortunately hibernate doesnt support Union. I was just wondering if there is any other way to achieve it else ill have to write it in a procedure and save the data in temp table and fire a sql to read data from that temp table

推荐答案

还有union的替代方法，但它并不漂亮:

There is an alternative for union, but it is not pretty:

select distinct coalesce(x1.name, x2.name) as name,
       coalesce(x1.age, x2.age) as age,
       coalesce(x1.Address, x2.Address) as age,
       coalesce(x1.phoneNo, x2.phoneNo) as age,
from (select A.name, A.age, B.Address, C.phoneNo
      from table1 A join
           Table2 B
           on a.pkId = b.FkId join
           Table3 C 
           on b.pkId = c.FkId
     ) x1 full outer join
     (select A.name, A.age, B.Address, C.phoneNo
      from table4 A join
           Table5 B
           on a.pkId = b.FkId join
           Table3 C 
           on b.pkId = c.FkId
     ) x2
     on 1 = 0;  -- always false

我无法想象您为什么要表达这样的union.不过，我强烈建议您开始使用正确的显式join语法.

I can't imagine why you would want to express a union like this. I would highly recommend, though, that you start using proper, explicit join syntax.

这篇关于Oracle中的工会替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！