如何在数据库表中找到重复的条目？ [英] How do I find duplicate entries in a database table?

问题描述

以下查询将显示在 book表中重复的所有杜威十进制数：

The following query will display all Dewey Decimal numbers that have been duplicated in the "book" table:

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

但是，我想做的是让我的查询显示与重复条目相关的作者姓名（ book表和 author表由 author_id连接）。换句话说，上面的查询将产生以下内容：

However, what I'd like to do is have my query display the name of the authors associated with the duplicated entry (the "book" table and "author" table are connected by "author_id"). In other words, the query above would yield the following:

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

我想要显示的结果类似于以下内容：

What I'd like the results to display is something similar to the following:

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

非常感谢您提供的任何帮助。而且，以防万一，我正在使用Postgresql数据库。

Any help you can provide is greatly appreciated. And, in case it comes into play, I'm using a Postgresql DB.

更新：请注意， author_last_name不在

UPDATE: Please note that "author_last_name" is not in the "book" table.

推荐答案

嵌套查询即可完成工作。

A nested query can do the job.

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

（我不知道这是否是实现您想要的最快方法。）

(I don't know if this is the fastest way to achieve what you want.)

更新：这是我的数据

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

这篇关于如何在数据库表中找到重复的条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！