Oracle中exists和直连

在修改条sql 的时候，需要将exists修改成直连，sql如下：

select substr(xzqhdm, 1, 6) xzqhdm,
count(b12) b12,
count(b15) b15,
count(b19) b19,
count(b22) b22
from (select csb.xzqhdm,
case
when sb.sfcb = ‘1’ and hd.hdlx = ‘20’ and
qz.sfczknry = ‘1’ and sb.jyknlx = ‘02’ then
hd.grbh
end b12,
case
when (sb.sfcb = ‘0’ or sb.sfcb is null) and
hd.hdlx = ‘20’ and qz.sfczknry = ‘1’ and
sb.jyknlx = ‘02’ then
hd.grbh
end b15,
case
when sb.sfcb = ‘1’ and hd.hdlx = ‘10’ and
qz.sfncknry = ‘1’ and sb.jyknlx = ‘03’ then
hd.grbh
end b19,
case
when (sb.sfcb = ‘0’ or sb.sfcb is null) and
hd.hdlx = ‘10’ and qz.sfncknry = ‘1’ and
sb.jyknlx = ‘03’ then
hd.grbh
end b22
from zyjs_gr_knryhdxxb hd,
view_zyjs_syqzxx_jxh qz,
zyjs_gr_sbjncgjgjzb sb, —
ldlsc_xtcsb csb,
(select grqzid, grqzdjsj, djsj
from zyjs_tj_gzjlb g –
where gzjg = ‘13’
and tjxlx = ‘01’
and g.djsj = (select max(djsj)
from zyjs_tj_gzjlb g1 –
where g1.grqzid = g.grqzid
and g1.grqzdjsj = g.grqzdjsj
group by g1.grqzid)) gz
where hd.grbh = qz.grbh
and qz.grqzid = sb.grqzid
and qz.djsj = sb.qzdjsj
and qz.grqzid = gz.grqzid
and qz.djsj = grqzdjsj
and csb.dwid = hd.djjg
and qz.djsj <= nvl(hd.hdzxsj, ‘99999999999999’)
and qz.djsj >= hd.hdzjsj
and ((qz.djsj >= ‘20171225000000’ and
qz.djsj <= ‘20181124235959’) or
qz.djsj < ‘20171225000000’)
and exists (select 1
from view_zyjs_grxy xy
where qz.grqzid = xy.grqzid
and xy.qzdjsj = qz.djsj)
and sb.hdksyf like ‘2018%’)
group by substr(xzqhdm, 1, 6)) a1 on re.xzqhdm = a1.xzqhdm

查出来的数据如下

首先修改的时候，我直接将将加粗部分的视图view_zyjs_grxy xy直连到上面查询到表的最后
from zyjs_gr_knryhdxxb hd,
view_zyjs_syqzxx_jxh qz,
zyjs_gr_sbjncgjgjzb sb, —
ldlsc_xtcsb csb,
from view_zyjs_grxy xy

然后在exists的位置上放入限定条件
qz.grqzid = xy.grqzid and xy.qzdjsj = qz.djsj
发现数据查询出来的数据是多余exists的

然后发现是在exists的 view_zyjs_grxy xy这个视图里面根据限定条件的数据是有多条的。
表直连的时候是如果a表有4条，b表有8条，假如a表中每条数据与b表中两条数据读对应，那就会把b表中的数据都查出来，有一条取一条
用exists的时候如上面那种情况就会只取一条，所以想让两种方法达到相同的效果，就应该对view_zyjs_grxy xy视图进行去重

修改后的sql如下
select substr(xzqhdm, 1, 6) xzqhdm,
count(b12) b12,
count(b15) b15,
count(b19) b19,
count(b22) b22
from (select csb.xzqhdm,
case
when sb.sfcb = ‘1’ and hd.hdlx = ‘20’ and
qz.sfczknry = ‘1’ and sb.jyknlx = ‘02’ then
hd.grbh
end b12,
case
when (sb.sfcb = ‘0’ or sb.sfcb is null) and
hd.hdlx = ‘20’ and qz.sfczknry = ‘1’ and
sb.jyknlx = ‘02’ then
hd.grbh
end b15,
case
when sb.sfcb = ‘1’ and hd.hdlx = ‘10’ and
qz.sfncknry = ‘1’ and sb.jyknlx = ‘03’ then
hd.grbh
end b19,
case
when (sb.sfcb = ‘0’ or sb.sfcb is null) and
hd.hdlx = ‘10’ and qz.sfncknry = ‘1’ and
sb.jyknlx = ‘03’ then
hd.grbh
end b22
from zyjs_gr_knryhdxxb hd,
view_zyjs_syqzxx_jxh qz,
zyjs_gr_sbjncgjgjzb sb, —
ldlsc_xtcsb csb,
(select grqzid, grqzdjsj, djsj
from zyjs_tj_gzjlb g –
where gzjg = ‘13’
and tjxlx = ‘01’
and g.djsj = (select max(djsj)
from zyjs_tj_gzjlb g1 –
where g1.grqzid = g.grqzid
and g1.grqzdjsj = g.grqzdjsj
group by g1.grqzid)) gz,
(select qzdjsj,grqzid,qdjg from view_zyjs_grxy group by qzdjsj,grqzid,qdjg) xy
where hd.grbh = qz.grbh
and qz.grqzid = sb.grqzid
and qz.djsj = sb.qzdjsj
and qz.grqzid = gz.grqzid
and qz.djsj = grqzdjsj
and csb.dwid = xy.qdjg
and qz.djsj <= nvl(hd.hdzxsj, ‘99999999999999’)
and qz.djsj >= hd.hdzjsj
and ((qz.djsj >= ‘20171225000000’ and
qz.djsj <= ‘20181124235959’) or
qz.djsj < ‘20171225000000’)
and qz.grqzid = xy.grqzid
and xy.qzdjsj = qz.djsj
and sb.hdksyf like ‘2018%’)
group by substr(xzqhdm, 1, 6)