理论上相同数据个别字段值不同重复问题:1.Oracle ---> 某字段重复,其他字段值不同时,按重复字段分组只取一条的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.Mysql ---> 某字段重复,其他字段值不同时,按重复字段分组只取一条的sql语句
(eg:相同的数据某个字段值有差别导致存储两条或多条无意义重复数据的情况)
select * from (
select *,@rank:=case when @current_id<>[重复字段] then 1 else @rank+1 end as rank,
@current_id:=[重复字段] from table_name
order by [重复字段],[不同字段] desc
) t where t.rank = 1
3.某字段重复,其他字段不同,取重复字段值并去重sql语句(第二句复制过来的,未测试)
(1)select distinct a.id from a
(2)select 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)