从SQL Join中删除重复项

Question

下面是一个类似于我真正问题的虚拟情况。Table1

recid   firstname    lastname   company
1       A             B          AAA
2       D             E          DEF
3       G             H          IJK
4       A             B          ABC

我有一个表格2，看起来像这样

recid   firstname    lastname   company
10      A             B          ABC
20      D             E          DEF
30      M             D          DIM
40      A             B          CCC

如果我将表格与 recid 相关联，那么结果将会是0个结果，因为recid 是唯一的。但如果我将其与不唯一且存在重复的 firstname 和 lastname 列相连，那么在内部连接中将会得到重复项。当我在连接中添加更多列时，情况会变得更糟（会产生更多的重复项）。

在上述简单情况中，如何从以下查询中删除重复项？ 我想要比较table2中的firstname和lastname，如果它们匹配，则返回table2中的firstname，lastname和recid。

SELECT DISTINCT * FROM
(SELECT recid, first, last FROM table1) a
INNER JOIN
(SELECT recid, first,last FROM table2) b
ON a.first = b.first

---

如果有人想要在将来尝试运行这个脚本，可以在这里找到它。

CREATE TABLE table1 (recid INT NOT NULL PRIMARY KEY, first varchar(20), last varchar(20), company varchar(20))
CREATE TABLE table2 (recid INT NOT NULL PRIMARY KEY, first varchar(20), last varchar(20), company varchar(20))

INSERT INTO TABLE1 VALUES(1,'A','B','ABC')
INSERT INTO TABLE1 VALUES(2,'D','E','DEF')
INSERT INTO TABLE1 VALUES(3,'M','N','MNO')
INSERT INTO TABLE1 VALUES(4,'A','B','ABC')

INSERT INTO TABLE2 VALUES(10,'A','B','ABC')
INSERT INTO TABLE2 VALUES(20,'D','E','DEF')
INSERT INTO TABLE2 VALUES(30,'Q','R','QRS')
INSERT INTO TABLE2 VALUES(40,'A','B','ABC')

- Hammad Khan

---

你需要recid吗？如果不需要，从查询中删除它就可以解决你的问题。 - Paddy

是的，我已经完成了其余部分。 - TheTechGuy

你需要为非重复行决定逻辑。对于重复项，CompanyId和RecId是不同的。你想保留哪一个？ - JNK

@Savage：所以，在上面的例子中，你的结果应该是具有 recid: 10,20,40 三行？ - ypercubeᵀᴹ

@Savage Garden：你试过我的查询吗？ - sll

在我的原始查询中，我使用了两个子查询，因为这两个查询在同一张表上运行，但是使用了不同的where子句。如果名字和姓氏在这两个子查询中匹配，我会返回这些名字、recid和另一个字段。 - TheTechGuy

Answer 1

你不需要执行一个join操作，你只是在测试是否存在/集合包含关系。

我不知道你正在编写哪种版本的SQL，但这应该有效。

SELECT MAX(recid), firstname, lastname 
FROM table2 T2
WHERE EXISTS (SELECT * FROM table1 WHERE firstname = T2.firstame AND lastname = T2.lastname)
GROUP BY lastname, firstname

如果你想要将其实现为join，但代码基本保持不变：

即，

SELECT max(t2.recid), t2.firstame, t2.lastname 
FROM Table2 T2 
INNER JOIN Table1 T1 
    ON T2.firstname = t1.firstname and t2.lastname = t1.lastname
GROUP BY t2.firstname, t2.lastname 

根据所使用的数据库管理系统，内连接和Exists（半连接 vs 连接）的实现方式可能不同，但优化器有时可以自动识别并选择正确的运算符，无论你以哪种方式编写它。

- Code Magician

---

他的示例代码中包含了从两个数据集中选择所有内容的 select * from 语句，但是他的描述中指出：“我想比较 firstname 和 lastname，如果它们匹配，我将返回 table2 中的 firstname、lastname 和 recid”。 - Code Magician

这就是它。我想我不需要join，但如果可能的话，有人能用join解释一下吗？ - TheTechGuy

他的示例代码使用SELECT DISTINCT *从一个INNER JOIN结果集中，很明显表明他想要来自两个表的数据。如果我错了并且他澄清了，我会很乐意删除DV。 - JNK

在我的情况下，它可以在没有MAX(recid)（原始查询）的情况下工作。第二个查询结果不同。谢谢。 - TheTechGuy

Answer 2

SELECT t2.recid, t2.first, t2.last 
FROM  table1 t1
INNER JOIN table2 t2 ON t1.first = t2.first AND t1.last = t2.last
GROUP BY t2.recid, t2.first, t2.last

编辑：添加图片

- sll

---

我尝试了类似于我的原始查询，但由于某些原因没有起作用，谢谢。 - TheTechGuy

@Savage Garden：你的意思是我提供的查询不起作用吗？这张图片代表了错误的结果集吗？ - sll

它确实可以工作。问题是我只想保持recid唯一。其余部分可以重复。比如我不想按t2.first和t2.last进行分组。但使用group by时，你必须使用它。 - TheTechGuy

Answer 3

有些情况下，您需要在子查询中进行分组。

SELECT distinct b.recid, b.first, b.last 
FROM table1 a
INNER JOIN (
  SELECT MAX(recid) as recid, first, last 
  FROM table2
  GROUP BY first, last
) b
ON a.first = b.first AND a.last = b.last

- robotik