一个表中有重复记录如何用SQL语句查询出来。。。?

Question

如题所述

Answer 1

select * from tablename where 重复字段1 in (select 重复字段1 from tablename group by 重复字段1,重复字段2 having count(*)>1)。

SQL重复记录查询方法：

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count (peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people 
where peopleId in (select   peopleId from people group by   peopleId   having count (peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段） 

select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having

Answer 2

不知道你什么数据库.
如果数据库支持 ROW_NUMBER() 函数的话, 倒是很省事的.

-- 首先创建测试表
CREATE TABLE test_delete(
name varchar(10),
value INT
);
go

-- 测试数据，其中 张三100 与 王五80 是完全一样的
INSERT INTO test_delete
SELECT '张三', 100
UNION ALL SELECT '张三', 100
UNION ALL SELECT '李四', 80
UNION ALL SELECT '王五', 80
UNION ALL SELECT '王五', 80
UNION ALL SELECT '赵六', 90
UNION ALL SELECT '赵六', 70
go

-- 首先查询一下， ROW_NUMBER 效果是否满足预期
SELECT
ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no,
name,
value
FROM
test_delete

no name value
----- ---------- -----------
1 李四 80
1 王五 80
2 王五 80
1 张三 100
2 张三 100
1 赵六 70
1 赵六 90

从结果上可以看到，如果有重复的，完全一样的话， no 是有大于1的。

Answer 3

select * from tablename where 重复字段1 in (select 重复字段1 from tablename group by 重复字段1,重复字段2 having count(*)>1)本回答被提问者和网友采纳

Answer 4

select name，count（name）as num from people group by name having num>1;

Answer 5

Select Row_Number() Over(Partition By 排序字段 Order By 不同的字段 Desc) As Num,
t.*
from table
where Num = 2