如何在数据库表中找到重复的条目？
以下查询将显示在 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