在数据库中查找重复行

Question

如何找到重复的行？如果以 last_name 字段为重复字段，则希望显示这些行。

last_name frst_name frst_name1 frst_name2 ....  

任何数据库都可以使用，最好选择Oracle。

- javaguy

---

你使用哪个数据库服务器？ - Matti Virkkunen

每个数据库都有自己的配方，但这些配方是特定于数据库的。 - Peter Tillemans

我的意思是使用不同的技巧来获取一份副本，但细节如row_id、row_num是不同的。但我误解了OP的问题。 - Peter Tillemans

Answer 1

这应该在几乎所有的 SQL 方言中都可以工作：

SELECT last_name, first_name FROM names
WHERE last_name IN (
    SELECT last_name FROM names GROUP BY last_name HAVING COUNT(*) > 1
)

但是这将会给你一个类似这样的结果集:

Smith     Jack
Smith     Joe
Smith     Anna
Sixpack   Joe
Sixpack   Eve

在我看来，以最优雅的方式显示所需格式的解决方案是在客户端应用程序中以编程方式重新排列结果集，而不是拉取各种晦涩的SQL技巧; 像这样（伪代码）：

for each row in resultset
   if row[last_name] <> previous_last_name
      print newline, print last_name
   print ' '
   print first_name

- tdammers

Answer 2

假设您的服务器已具备GROUP_CONCAT功能，因为您没有提及使用的是哪个服务器：

SELECT GROUP_CONCAT(first_name SEPARATOR ' ')
FROM table
GROUP BY last_name
HAVING COUNT(first_name) > 1

- Matti Virkkunen

---

我非常喜欢这个！不过，GROUP_CONCAT 目前仅在 MySQL 中实现了。以下链接提供了 Oracle 版本，并且该博客还有 PostGreSQL 和 SQL Server 的版本。http://explainextended.com/2009/04/05/group_concat-in-oracle-10g/ - eksortso

@eksortso：SQLite 还支持 GROUP_CONCAT 函数。 - OMG Ponies

Answer 3

哈，很多查询。这里有更多。

SELECT last_name, first_name FROM names n1
WHERE 
(
    SELECT count(*) FROM names n2 where n2.last_name = n1.last_name
) 
> 1

如果表有唯一标识符

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
) 

- Danil

Answer 4

Select a.* from persons a inner join persons b on (a.personID<>b.PersonID and a.last_name=b.last_name)

PersonID是您表格的主键。

- user455566

Answer 5

我不确定这是否是您要求的内容，但我认为您正在寻找的是

SELECT * FROM users u1, users u2 
WHERE (u1.last_name = u2.last_name AND COUNT(u1.last_name) > 1))

- dirbacke

---

如果你的姓非常普遍，那么这将会给你带来很多重复的结果。 - eksortso

我刚刚回答了这个问题... 如果您想检查其他字段，那么您只需要在WHERE子句中添加它们，并使用DISTINCT选项。 - dirbacke

无法在WHERE子句中使用聚合函数（例如：COUNT），除非在子查询中。修复该错误后，“SELECT *”将返回来自“USERS”表的两个副本的行。 - OMG Ponies

Answer 6

我尝试设计一个适用于大多数符合ANSI标准的SQL数据库服务器的解决方案。以下是我的想法。

这里的想法是，您可以识别重复的last_name，然后提取所有具有其中一个副本的记录。

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, ...
;

- eksortso

Answer 7

假设“在客户表中，您有客户键作为主键”，那么您可以使用以下代码：

select 
    customerkey,count(customerkey) 
from 
    customer 
group 
    by customerkey
having 
    count(customerkey)>1;

这将给出所有重复的customerkeys。现在你可以删除它们。

- Praveens