sql去重;同一条数据出现多条取一条的sql语句


理论上相同数据个别字段值不同重复问题:

1.某字段重复,其他字段值不同时,按重复字段分组只取一条的sql语句
(eg:相同的数据某个字段值有差别导致存储两条或多条无意义重复数据的情况)

select s.* from ( 
  select a.*, row_number() over (partition by [
重复字段] order

  by [不同字段]) as group_idx

from table_name a ) s 
where s.group_idx= 1

2.
某字段重复,其他字段不同,取重复字段值并去重sql语句(第二句复制过来的,未测试)

(1select distinct a.id from a
(2select id from a 
     where a.num = 1
     group by id
     having on count(id) > 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) 

 

 

 

 

 

 

阅读更多

没有更多推荐了,返回首页