查询表内多字段同时重复记录的SQL语句
-
查询表内多字段同时重复记录的SQL语句，⽐如现在有⼀⼈员表  （表名：peosons）
若想将姓名、⾝份证号、住址这三个字段完全相同的记录查询出来
select  p1.*  from  persons  p1,persons  p2  where  p1.id<>p2.id  and  p1.cardid  =  p2.cardid  and  p1.pname  =  p2.pname  and  p1.address  =  p2.address
可以实现上述效果．
⼏个删除重复记录的SQL语句
1.⽤rowid⽅法
2.⽤group by⽅法
3.⽤distinct⽅法
1。⽤rowid⽅法
据据oracle带的rowid属性，进⾏判断，是否存在重复，语句如下：
查数据:
select * from table1 a where rowid !=(select  max(rowid)
from table1 b where a.name1=b.name1 and a.name2=)sql语句查询不包含
删数据：
delete  from table1 a where rowid !=(select  max(rowid)
from table1 b where a.name1=b.name1 and a.name2=)
查数据:
  select count(num), max(name) from student --列出重复的记录数，并列出他的name属性
  group by num
  having count(num) >1 --按num分组后出表中num列重复，即出现次数⼤于⼀次
删数据：
  delete from student
  group by num
  having count(num) >1
  这样的话就把所有重复的都删除了。
3.⽤distinct⽅法 -对于⼩的表⽐较有⽤
create table table_new as  select distinct *  from table1 minux
truncate table table1;
insert into table1 select * from table_new;
查询及删除重复记录的⽅法⼤全
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 count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最⼩的记录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查表中多余的重复记录（多个字段），不包含rowid最⼩的记录
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。