mysql怎样实现查重只留一个
mysql实现查重只留一个的办法:第一通过“select * from”查寻表中余外的反复记载;然后通过“delete from”删除反复数据,并只保存一个数据即可。
举荐:《mysql视频教程》
mysql 删除反复数据只保存一笔记录
删除反复数据保存name中id最小的记载
delete from order_info where id not in (select id from (select min(id) as id from order_info group by order_number) as b);
delete from table where id not in (select min(id) from table group by name having count(name)>1) and id in (select id group by name having count(name)>1)
(注意:HAVING 子句对 GROUP BY 子句设定前提的方式与 WHERE 和 SELECT 的交互方式相似。WHERE 搜寻前提在进行分组操纵以前利用;而 HAVING 搜寻前提在进行分组操纵之后利用。HAVING 语法与 WHERE 语法相似,但 HAVING 可以包括聚合函数。HAVING 子句可以援用选中列表中显示的任意项。)
扩展:
SQL:删除反复数据,只保存一条用SQL语句,删除掉反复项只保存一条在几千笔记录里,存在着些雷同的记载,怎样能用SQL语句,删除掉反复的呢
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 peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>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)
6.消弭一个字段的左边的首先位:
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
7.消弭一个字段的右侧的首先位:
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
8.假删除表中余外的反复记载(多个字段),不包括rowid最小的记载
update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in (select 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)
以上就是mysql怎样实现查重只留一个的细致内容,更多请关注 百分百源码网 其它相干文章!