日期:2014-05-17 浏览次数:20830 次
y_card_no y_amt 银联_卡号 银联_金额
b_card_no b_amt 本行_卡号 本行_金额
y_card_no y_amt b_card_no b_amt 银联_卡号 银联_金额 本行_卡号 本行_金额
with yinlian as( select '001' y_card_no, 100 y_amt from dual union all select '001' y_card_no, 100 y_amt from dual union all select '002' y_card_no, 200 y_amt from dual union all select '002' y_card_no, 300 y_amt from dual union all select '003' y_card_no, 100 y_amt from dual), benhang as( select '001' b_card_no, 100 b_amt from dual union all select '002' b_card_no, 200 b_amt from dual union all select '003' b_card_no, 100 b_amt from dual) SELECT y_card_no, y_amt, b_card_no, b_amt FROM (SELECT row_number() over(PARTITION BY y_card_no ORDER BY y_card_no) rn, t.* FROM yinlian t) a, (SELECT row_number() over(PARTITION BY b_card_no ORDER BY b_card_no) rn, t.* FROM benhang t) b WHERE a.y_card_no = b.b_card_no(+) AND a.rn = b.rn(+);