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值