执行INNER JOIN时结果重复 [英] Duplicated results when performing INNER JOIN

问题描述

我有2个简单的表，我想使用它们进行INNER JOIN操作，但是问题是我正在复制(对于str1和str2列)结果:

I have 2 simple tables that I would like to perform an INNER JOIN with, but the problem is that I'm getting duplicated (for the columns str1 and str2) results:

CREATE TABLE #A (Id INT, str1 nvarchar(50), str2 nvarchar(50))
insert into #A  values (1, 'a', 'b')
insert into #A  values (2, 'a', 'b')

CREATE TABLE #B (Id INT, str1 nvarchar(50), str2 nvarchar(50))
insert into #B values (7, 'a', 'b')
insert into #B  values (8, 'a', 'b')

select * from #A a
INNER JOIN #B b ON a.str1 = b.str1 AND a.str2 = b.str2  

当我真正想要2时，它给了我4条记录.

It gave me 4 records when I really wanted 2.

我得到了什么:
id |str1 |str2 |id |str1 |str2
1 |一个|b |7 |一个|b
2 |一个|b |7 |一个|b
1 |一个|b |8 |一个|b
2 |一个|b |8 |一个|b

What I got:
id | str1 | str2| id | str1 | str2
1 | a | b | 7 | a | b
2 | a | b | 7 | a | b
1 | a | b | 8 | a | b
2 | a | b | 8 | a | b

我真正想要的是:
1个|b |7 |一个|b
2个|b |8 |一个|b

What I really wanted:
1 a | b | 7 | a | b
2 a | b | 8 | a | b

任何人都可以帮忙吗?我知道使用游标和循环是可以实现的，但是我想避免这种情况，并尽可能使用某种JOIN类型.

Can anyone help? I know this is achievable using a cursor and loop, but I'd like to avoid it and only use some type of JOIN if possible.

推荐答案

SELECT 
    a.id AS a_id, a.str1 AS a_str1, a.str2 AS a_str2, 
    b.id AS b_id, b.str1 AS b_str1, b.str2 AS b_str2
FROM 
    ( SELECT *
           , ROW_NUMBER() OVER (PARTITION BY str1, str2 ORDER BY id) AS rn
      FROM #A 
    ) a
  INNER JOIN 
    ( SELECT *
           , ROW_NUMBER() OVER (PARTITION BY str1, str2 ORDER BY id) AS rn
      FROM #B 
    ) b 
    ON  a.str1 = b.str1 
    AND a.str2 = b.str2 
    AND a.rn = b.rn ;

如果您在一个或其他表中具有相同(str1，str2)组合的更多行，则可以通过更改 INNER 连接来选择要返回的行加入 LEFT ， RIGHT 或 FULL 加入.

If you have more rows in one or the other tables for the same (str1, str2) combination, you can choose which ones will be returned by changing INNER join to either LEFT, RIGHT or FULL join.

这篇关于执行INNER JOIN时结果重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！