如何在数据库表中查找重复的条目？

Question

以下查询将显示在"book"表中被重复的所有杜威十进分类号：

SELECT dewey_number, 
 COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY dewey_number
HAVING ( COUNT(dewey_number) > 1 )

然而，我想要做的是让我的查询显示与重复条目相关联的作者的名称（“book”表和“author”表通过“author_id”相互连接）。换句话说，上面的查询将产生以下结果：

dewey_number | NumOccurrences
------------------------------
5000         | 2
9090         | 3

我希望结果的显示类似于以下内容：

author_last_name | dewey_number | NumOccurrences
-------------------------------------------------
Smith            | 5000         | 2
Jones            | 5000         | 2
Jackson          | 9090         | 3
Johnson          | 9090         | 3
Jeffers          | 9090         | 3

非常感谢您提供的任何帮助。如果有必要，我正在使用Postgresql数据库。

更新：请注意，“author_last_name”不在“book”表中。

- Huuuze

---

可以有人关闭这个问题吗？这是重复的 https://dev59.com/dXVD5IYBdhLWcg3wU56H。 - Binary Worrier

请仔细阅读，这并非重复内容。 - Huuuze

完全不是重复的。他想要找到重复的行（以及更多），而不是删除它们。 - Federico A. Ramponi

Answer 1

一个嵌套查询可以完成这项工作。

SELECT author_last_name, dewey_number, NumOccurrences
FROM author INNER JOIN
     ( SELECT author_id, dewey_number,  COUNT(dewey_number) AS NumOccurrences
        FROM book
        GROUP BY author_id, dewey_number
        HAVING ( COUNT(dewey_number) > 1 ) ) AS duplicates
ON author.id = duplicates.author_id

(我不知道这是否是实现你想要的最快方法。)

更新：这是我的数据

SELECT * FROM author;
 id | author_last_name 
----+------------------
  1 | Fowler
  2 | Knuth
  3 | Lang

SELECT * FROM book;
 id | author_id | dewey_number |         title          
----+-----------+--------------+------------------------
  1 |         1 |          600 | Refactoring
  2 |         1 |          600 | Refactoring
  3 |         1 |          600 | Analysis Patterns
  4 |         2 |          600 | TAOCP vol. 1
  5 |         2 |          600 | TAOCP vol. 1
  6 |         2 |          600 | TAOCP vol. 2
  7 |         3 |          500 | Algebra
  8 |         3 |          500 | Undergraduate Analysis
  9 |         1 |          600 | Refactoring
 10 |         2 |          500 | Concrete Mathematics
 11 |         2 |          500 | Concrete Mathematics
 12 |         2 |          500 | Concrete Mathematics

以下是上述查询的结果：

 author_last_name | dewey_number | numoccurrences 
------------------+--------------+----------------
 Fowler           |          600 |              4
 Knuth            |          600 |              3
 Knuth            |          500 |              3
 Lang             |          500 |              2

- Federico Ramponi

---

好的，可能嵌套查询有些过度了，但现在我确定它可以工作。Tony Andrews对author_id列的评论在这里也适用。 - Federico A. Ramponi

顺便问一下，你为什么要根据作者姓名和杜威编码来搜索重复项？ 重复项（我猜是同一本书的多个副本）也应该基于书名... - Federico A. Ramponi

我更新了代码，但是它返回0个结果。至于用例问题，我只是创建了一个虚拟场景--我实际上并没有在检测重复图书方面工作。 - Huuuze

更新您的数据，使得author_id=1只有一个“600”，而author_id=2也只有一个“600”。您是否仍然可以得到“600”的结果？ - Huuuze

当然不行。否则你的问题就是错误的。你不能期望一个查询从那些（很多）具有dewey=600条目的作者中任意选择一个author_id，并将其与作者表连接。 - Federico A. Ramponi

Answer 2

你可能需要这个

SELECT dewey_number, author_last_name,
 COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY dewey_number,author_last_name
HAVING ( COUNT(dewey_number) > 1 )

- Kibbee

---

"author_last_name" 不在 "book" 表中。"book" 表和 "author" 表通过 "book" 表中的 FKed "author_id" 相连。 - Huuuze

Answer 3

SELECT dewey_number, author_last_name,
       COUNT(dewey_number) AS NumOccurrences
FROM book
JOIN author USING (author_id)
GROUP BY dewey_number,author_last_name
HAVING COUNT(dewey_number) > 1

如果book.author_id可以为空，则更改连接方式为：

LEFT OUTER JOIN author USING (author_id)

如果每个表中的author_id列名称不同，则无法使用USING，而应该使用ON：

JOIN author ON author.id = book.author_id

或者

LEFT OUTER JOIN author ON author.id = book.author_id

- Tony Andrews

---

这个不行，它返回0个结果。 - Huuuze

两个表都有你所说的AUTHOR_ID列吗？我的USING语法是基于这个假设的。如果没有，请改为“JOIN author ON author.xxx = book.yyy”。如果查询返回0行（没有错误），那么这意味着作者表是空的？ - Tony Andrews

我认为我们已经接近成功了。我忘了提到它是“author.id”和“book.author_id”，所以你在使用USING和ON方面的选择很好。我已经更新了，但是Postgresql不喜欢方括号“[LEFT JOIN]”。在去掉方括号后，它返回0个结果。 - Huuuze

而且，不，两个“id”字段都不能为null。 - Huuuze

抱歉，我在LEFT OUTER周围添加括号是为了表示这些关键词是可选的。 - Tony Andrews

Answer 4

select author_name,dewey_number,Num_of_occur
from author a,(select author_id,dewey_number,count(dewey_number) Num_of_occur
                from   book
                group by author_id,dewey_number
                having count(dewey_number) > 1) dup
where a.author_id = dup.author_id

- user5104009

---

除了提供代码，您能否解释为什么这个答案会起作用？ - Greg Viers

您会收到一个错误，因为author_last_name没有在group by或聚合函数中。 - Greg Viers

Answer 5

我发现最简单和有效的方法如下：

SELECT
    p.id
    , p.full_name
    , (SELECT count(id) FROM tbl_documents as t where t.person_id = p.id) as rows
FROM tbl_people as p
WHERE 
    p.id 
IN (SELECT d.person_id FROM tbl_documents as d 
    GROUP BY d.person_id HAVING count(d.id) > 1) 
ORDER BY 
    p.full_name

- Gabriel Glauber

Answer 6

select * from author
dewey_number    author_last_name
1   Ramu
2   Rajes
1   Samy
1   Ramu

select * from book
authorid    dewey_number
1   1
2   1

select a.dewey_number,a.author_last_name,count(a.dewey_number) from author a
where a.dewey_number in (
select b.dewey_number from book b )
group by a.dewey_number,a.author_last_name

dewey_number    author_last_name    (No column name)
1   Ramu    2
1   Samy    1

- Nilofar