SQL 中的模糊一一匹配

来源：互联网发布：大数据咨询公司编辑：程序博客网时间：2024/05/17 02:29

Table test1:

ID NAME VOLUMN

----------------------------------------------------------------------- ---------------------------

1 A1 1000

2 A2 1000

3 A3 1000

Table test2

ID NAME VOLUMN

----------------------------------------------------------------------- ---------------------------

1 B1 1000

2 B2 1000

3 B3 1000

if we just inner join them by same Volumn, will got 9 records,

select test1.id id1, test2.id id2
from test1, test2 where test1.volumn= test2.volumn;

ID1 ID2
--------------------------------------- ---------------------------------------
3 1
2 1
1 1
3 2
2 2
1 2
3 3
2 3
1 3

But use below sql, we can get oneone match ids:

select * from (

select test1.id id1,test2.id id2, row_number() over(partition by test1.id order by test2.id)order1, row_number() over(partition by test2.id order by test1.id) order2

from test1, test2 wheretest1.volumn= test2.volumn

) where order1 =order2;

ID1 ID2 ORDER1 ORDER2

------------------------------------------------------------------------------ ------------------------------------------------------------------------------

1 1 1 1

2 2 2 2

3 3 3 3

0 0