17,088
社区成员
发帖
与我相关
我的任务
分享
with t1 as
(
select '123' callera,'000' calleda from dual
union all
select '124' callera,'111' calleda from dual
union all
select '125' callera,'000' calleda from dual
union all
select '125' callera,'111' calleda from dual
union all
select '126' callera,'222' calleda from dual
union all
select '127' callera,'333' calleda from dual
),t2 as
(
select '000' calledb,'aaa' smscontent from dual
union all
select '111' calledb,'bbb' smscontent from dual
union all
select '000' calledb,'ccc' smscontent from dual
union all
select '222' calledb,'ddd' smscontent from dual
)
select b.calledb,a.callera,b.smscontent
from (
select rownum r,calledb,smscontent,row_number() over(partition by calledb order by rownum) rn
from t2
) b left join
(
select calleda,callera,row_number() over(partition by calleda order by rownum) rn
from t1
) a on b.calledb = a.calleda and b.rn = a.rn
order by b.r
calledb callera smscontent
-------------------------------------
1 000 123 aaa
2 111 124 bbb
3 000 125 ccc
4 222 126 ddd
select b.calledb,a.callera,b.smscontent from b,a where b.calledb=a.calleda
--没测过,不知道可不可以