查询和删去表中重复数据sql句子(一)ITeye - 凯发娱乐

查询和删去表中重复数据sql句子(一)ITeye

2019年03月25日15时23分10秒 | 作者: 紫安 | 标签: 重复,表中,记载 | 浏览: 1958

1、查询表中重复数据。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、查找表中剩余的重复记载(多个字段)

elect * 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)
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章