oracle 在数据库中查找重复行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3773732/
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
Find duplicate rows in database
提问by javaguy
How do find duplicate rows? If last_name is the duplicate field, I want to have to display
如何找到重复的行？如果 last_name 是重复字段，我想必须显示
last_name frst_name frst_name1 frst_name2 ....
Any database will do, prefer oracle.
任何数据库都可以，更喜欢 oracle。
回答by tdammers
This should work on pretty much every SQL dialect:
这应该适用于几乎所有 SQL 方言：
SELECT last_name, first_name FROM names
WHERE last_name IN (
SELECT last_name FROM names GROUP BY last_name HAVING COUNT(*) > 1
)
It will give you a result set like this, though:
不过，它会给你一个这样的结果集：
Smith Hyman
Smith Joe
Smith Anna
Sixpack Joe
Sixpack Eve
The most elegant solution for displaying this in the desired format IMO is to just re-arrange the result set programmatically in the client application instead of pulling all sorts of obscure SQL stunts; something like (pseudocode):
以所需的格式显示 IMO 的最优雅的解决方案是在客户端应用程序中以编程方式重新排列结果集，而不是拉各种晦涩的 SQL 噱头；类似（伪代码）：
for each row in resultset
if row[last_name] <> previous_last_name
print newline, print last_name
print ' '
print first_name
回答by Matti Virkkunen
Assuming your server has GROUP_CONCAT because you didn't mention which one you're using:
假设您的服务器有 GROUP_CONCAT，因为您没有提到您使用的是哪一个：
SELECT GROUP_CONCAT(first_name SEPARATOR ' ')
FROM table
GROUP BY last_name
HAVING COUNT(first_name) > 1
回答by Danil
Ha, lots of queries. Here is more
哈，很多疑问。这里有更多
SELECT last_name, first_name FROM names n1
WHERE
(
SELECT count(*) FROM names n2 where n2.last_name = n1.last_name
)
> 1
or if table has unique identifier
或者如果表有唯一标识符
SELECT last_name, first_name FROM names n1
WHERE exists
(
SELECT id FROM names n2 where n2.last_name = n1.last_name and n1.id <> n2.id
)
回答by user455566
Select a.* from persons a inner join persons b on (a.personID<>b.PersonID and a.last_name=b.last_name)
PersonID is your table's primary key.
PersonID 是您的表的主键。
回答by dirbacke
I do not know if this is what you are asking for, but I think what you are looking for is
我不知道这是否是您要的，但我认为您要找的是
SELECT * FROM users u1, users u2
WHERE (u1.last_name = u2.last_name AND COUNT(u1.last_name) > 1))
回答by eksortso
I tried to devise a solution that would work in most ANSI-compliant SQL database servers. Here's what I came up with.
我试图设计一个可以在大多数符合 ANSI 标准的 SQL 数据库服务器中工作的解决方案。这是我想出的。
The idea here is that you identify the duplicated last_names, then pull all the records that have one of those duplicates.
这里的想法是您识别重复的last_names，然后拉出所有具有这些重复之一的记录。
SELECT
t.last_name, t.frst_name, t.frst_name1, t.frst_name2, ...
FROM our_table AS t
WHERE t.last_name IN (
SELECT t0.last_name
FROM our_table AS t0
GROUP BY t0.last_name
HAVING COUNT(*) > 1
)
ORDER BY
t.last_name, t.frst_name, t.frst_name1, t.frst_name2, ...
;
回答by Praveens
Suppose "In table customer you have customerkey as PK" then you can use:
假设“在表 customer 你有 customerkey 作为 PK”，那么你可以使用：
select
customerkey,count(customerkey)
from
customer
group
by customerkey
having
count(customerkey)>1;
This will give you all the duplicate customerkeys. Now you can delete them.
这将为您提供所有重复的客户密钥。现在您可以删除它们。
