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

2024-11-09 15:56:53

推荐回答（5个）

回答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

回答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的。

回答3：

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

回答4：

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

回答5：

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