以下查询将显示在“书”表中重复的所有杜威十进制数：
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 DB。
更新 ：请注意，“ book”表中没有“ author_last_name”。
嵌套查询可以完成这项工作。
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
