日期:2014-05-16  浏览次数:20362 次

oracle中distinct关键字导致结果的再一次inner join失败的问题
         select a.*,c.* from
         (select jx.jxdxdh, jx.zhdh, jx.zhhm from jxdx_ckzh jx) a
         inner join
         (select /*distinct*/ b.jxdxdh,b.fpms
            from (select gx.jxdxdh,
                         wmsys.wm_concat(hy.hymc || gx.zlbl) fpms
                    from khdx_hy hy, dxgx_hyyjgx_ck gx
                   where hy.khdxdh = gx.khdxdh
                     and 20130421 between gx.qsrq and gx.jsrq
                     and gx.gxhslx = '1'
                   group by gx.jxdxdh
                  union all
                  select jx.jxdxdh, hy.hymc || '|100' as fpms
                    from jxdx_ckzh jx, khdx_hy hy
                   where hy.khdxdh = jx.khdxdh
                     and 20130421 > jx.khrq
                     and jx.gxhslx = '0') b) c
                     on a.jxdxdh = c.jxdxdh;                  

oracle中为什么上述语句中加distinct关键字后,会导致查询结果集数据丢失?
union all 中的第1部分的所有数据都丢失了!!不知道为何,我怀疑是使用distinct后,
就不能使用inner join关联结果集了,如此distinct只能放到最后。