oracle连接消除重复项,oracle 多表连接时消除重复

with A as (select aa.eqid,aa.usedate, aa.field,aa.track,m.name,ROW_NUMBER()OVER(PARTITION BY eqid ORDER BY usedate )RN from r_device_one aa left join  b_model m on aa.modelid = m.id where aa.eqid= '1'),

B as (select aa.eqid, tt.bh, tt.issuedtime, tt.operator, tt.issuedtypeid,dd.name xlname,ff.name, ROW_NUMBER()OVER(PARTITION BY aa.eqid ORDER BY tt.issuedtime )RN

from R_DEVICE_one aa

left JOIN t_WW tt

on aa.eqid between tt.eqidstart and tt.eqidend

join B_BASICDATA dd

on tt.xlstateid = dd.id

and dd.type = '线路状态'

left join T_repair_TWO cc on aa.eqid = cc.eqid and tt.bh = cc.inspectionid

left join  B_BASICDATA ff on cc.operate = ff.id and ff.type = '故障类型'

where aa.eqid = '1'),

C as (select rr.eqid,rr.bh, rr.issuedtime,rr.operator,rr.confirmer ,rr.explain,dd.name,ROW_NUMBER()OVER(PARTITION BY eqid ORDER BY issuedtime )RN from t_repair_TWO rr left join  B_BASICDATA dd

on rr.operate = dd.id and dd.type = '故障类型' where rr.eqid = '1')

SELECT AA.*,BB.bh B_BH,BB.issuedtime B_issuedtime, BB.operator B_operator, BB.issuedtypeid B_issuedtypeid,BB.xlname B_xlname, BB.name B_name,CC.BH c_BH,CC.issuedtime c_issuedtime,CC.operator c_operator,CC.explain,CC.name C_NAME ,CC.confirmer FROM A AA

full JOIN B BB ON AA.eqid=BB.eqid and AA.RN = BB.RN

full JOIN C CC ON BB.eqid=CC.eqid and BB.RN = CC.RN

本文最关键的在于分组排序利用RN值