sql查询数据库表中重复数值

sql查询数据库表中重复数值

-- 查询表中id重复的值
select id from 表名 group by id having count(*) > 1

--查询表中的重复记录，重复记录是根据id重复做判定
select * from 表名 where id in(select id from 表名 group by id having count(*) > 1)

-- 删除表中多余的重复记录，重复记录根据id重复做判定，只留rowid最小的那条记录
delete from 表名 where (id) in (select id from 表名 group by id having count(id) > 1) and rowid not in(select min(rowid) from group by id having count(*) > 1)

-- 查找表中多余的重复记录（多个字段）
select * from 表名 a where (a.id,a.name) in (select id, name from 表名 group by id,name having count(*) > 1) 

-- 查询表中多余的重复记录（多个字段）,不包含rowid最小的记录
select * from 表名 a where (a.id,a.name) in (select id,name from 表名 group by id,name having count(*) > 1) and rowid not in (select min(rowid) from 表名 group by id,name having count(*) > 1)