oracle如何将这些 2 的查询合并到一个查询,而无需使用联盟

标签: sql Oracle
发布时间: 2017/4/15 19:08:09
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.
SELECT b.KPCNO
         ,b.KPC_FULL_NAME    

FROM xxkpc_hr_personnel_v2  b
WHERE  b.type(+) = 'KPC Employee'
       AND b.DESIGNATION is null
       AND b.kpcno IS NOT NULL
       AND b.ORGANIZATION_ID=(select g.ORGANIZATION_ID from  xxkpc_fn_web_personnel_v g where g.kpcno = :kpcno)
GROUP BY b.KPCNO
          ,b.kpc_full_name

UNION

SELECT b.KPCNO
         ,b.KPC_FULL_NAME
FROM xxkpc_hr_pos_struct_code_comb   a
     ,xxkpc_hr_personnel_v2  b
WHERE b.position_id(+) = a.child_position_id
       AND b.type(+) = 'KPC Employee'
       AND b.kpcno IS NOT NULL
       AND a.PERANT_POSITION_ID =  (select g.POSITION_ID from  xxkpc_fn_web_personnel_v g where g.kpcno = :kpcno)
GROUP BY b.KPCNO
          ,b.kpc_full_name

请我需要将这些 2 的查询组合到一个查询,而无需使用联盟

解决方法 1:

我想你可以写它喜欢︰

select distinct b.kpcno, b.kpc_full_name
  from xxxkpc_hr_personnel_v2 b
 where b.type(+) = 'KPC Employee'
   and b.kpcno is not null
   and (
         (b.designation is null and
          b.organization_id in (select g.organization_id
                                  from xxkpc_fn_web_personnel_v g 
                                 where g.kpcno = :kpcno))
         or
        (b.position_id in (select a.child_position_id
                             from xxkpc_hr_pos_struct_code_comb a
                            where a.parent_position_id = (select g.position_id
                                                            from xxkpc_fn_web_personnel_v g 
                                                           where g.kpcno = :kpcno)))
       )

就我所知你正在使用 group by ,使结果 distinct ,所以适当的键词。ab 不离开加入,因为在下哪里 and ed 条款你说 a.parent_position_id = ....

赞助商