SQL 如何在数据库表中找到重复的条目？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/435964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find duplicate entries in a database table?
提问by Huuuze
The following query will display all Dewey Decimal numbers that have been duplicated in the "book" table:
以下查询将显示“book”表中重复的所有杜威十进制数：
SELECT dewey_number,
COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY dewey_number
HAVING ( COUNT(dewey_number) > 1 )
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:
但是，我想要做的是让我的查询显示与重复条目关联的作者姓名（“book”表和“author”表通过“author_id”连接）。换句话说，上面的查询将产生以下结果：
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
Hymanson | 9090 | 3
Johnson | 9090 | 3
Jeffers | 9090 | 3
Any help you can provide is greatly appreciated. And, in case it comes into play, I'm using a Postgresql DB.
非常感谢您能提供的任何帮助。而且，万一它发挥作用，我正在使用 Postgresql DB。
UPDATE: Please note that "author_last_name" is not in the "book" table.
更新：请注意“author_last_name”不在“book”表中。
采纳答案by Federico A. Ramponi
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.)
（我不知道这是否是实现您想要的最快方法。）
Update: Here is my data
更新：这是我的数据
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
And here is the result of the above query:
这是上述查询的结果：
author_last_name | dewey_number | numoccurrences
------------------+--------------+----------------
Fowler | 600 | 4
Knuth | 600 | 3
Knuth | 500 | 3
Lang | 500 | 2
回答by Kibbee
You probably want this
你可能想要这个
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 )
回答by Tony Andrews
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
If book.author_id can be null then change the join to:
如果 book.author_id 可以为 null，则将连接更改为：
LEFT OUTER JOIN author USING (author_id)
If the author_id column has a different name in each table then you can't use USING, use ON instead:
如果 author_id 列在每个表中都有不同的名称，那么您不能使用 USING，而是使用 ON：
JOIN author ON author.id = book.author_id
or
或者
LEFT OUTER JOIN author ON author.id = book.author_id
回答by user5104009
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
回答by Gabriel Glauber
Most simple and efective way i found is show below:
我发现的最简单有效的方法如下所示：
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
回答by Nilofar
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
