查找重复项的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16687853/
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
SQL query to find duplicates
提问by John Doe
I am trying to write a query in SQL server to find out if there are any multiple rows for each hash value.
I need all filenames where the hash value has duplicates.
我正在尝试在 SQL 服务器中编写一个查询，以确定每个哈希值是否有多个行。
我需要哈希值重复的所有文件名。
The result should be (based on my example below)
结果应该是（基于我下面的例子）
003B4C68BC143B0290E04432A3A96092 File0003.jpg
003B4C68BC143B0290E04432A3A96092 File0004.jpg
003B4C68BC143B0290E04432A3A96092 File0005.jpg
Please let me know.
请告诉我。
Here is the table structure
这是表结构
File table
-----------------------------------------
hash FileName
---------------------------------------
000341A486F5492877D588BED0806650 File0001.jpg
00363EF2ECEEA32F10176EB64A50283F File0002.jpg
003B4C68BC143B0290E04432A3A96092 File0003.jpg
003B4C68BC143B0290E04432A3A96092 File0004.jpg
003B4C68BC143B0290E04432A3A96092 File0005.jpg
回答by Rapha?l Althaus
select *
from File
where hash in (select
hash
from File
group by hash
having count(*) > 1)
回答by John Woo
You can use EXISTSto check for duplicates,
您可以EXISTS用来检查重复项，
SELECT a.*
FROM TableName a
WHERE EXISTS
(
SELECT 1
FROM Tablename b
WHERE a.hash = b.hash
GROUP BY hash
HAVING COUNT(*) > 1
)
or INNER JOIN
或者 INNER JOIN
SELECT a.*
FROM [File] a
INNER JOIN
(
SELECT hash
FROM [File] b
GROUP BY hash
HAVING COUNT(*) > 1
) b ON a.hash = b.hash
