今天接到客户的需求，要写一个查询，主要是找重复记录的，并且要将重复记录按不同的重复类别分开，不显示没有重复的记录，左写右写，都要分组，但按要求分组后就显示不出来数据。没有办法，拿出With as一改，Ｏｋ了！
with a as
(select case when a.jsdid=b.jsdid and a.mxid=b.mxid then '本条'
when a.jsdid=b.jsdid and a.mxidb.mxid then '本单'
when a.jsdidb.jsdid and b.shbj>0then '外单'
endcflb,
a.mxid grup,
f.jsdh jsdh,
b.mxid mxid,
to_char(b.rq,'yyyy-mm-dd')rq,
b.hbh,
b.jh,
c_hdmc(b.hdidid)hdmc,
b.sl1,
b.sl2,
b.sl3,
b.biaoz,
b.je,
b.shsl1,
b.shsl2,
b.shsl3,
b.biaoz2,
b.je2,
b.sssl1,
b.sssl2,
b.sssl3,
b.ssbz,
b.ssje
from dwxxb c,fyjsd e,lrmxfy a,dwxxb d,fyjsd f,lrmxfy b
wherea.jsdid=c_jsdid('dpqtgzjc05020800')
and c.dwid=e.dwid
and e.jsdid=a.jsdid
and d.dwid=f.dwid
and f.jsdid=b.jsdid
and a.rq=b.rq
and a.hbid=b.hbid
and a.jhid=b.jhid
and a.jcid=b.jcid
and a.fyid=b.fyid
and a.jxid=b.jxid
and a.hdidid=b.hdidid
)
select * from a where grup in (
selectgrup from a
group by grup
having count(grup)>1)
order by grup,cflb desc,mxid
这样，既解决了分组问题，也只查询一次，爽啊！
|
