delete from test.tb1 using(test.tb1,test.tb2) where test.tb1.col1=test.tb2.col2
(1)删除表中多余的重复记录,重复记录是根据单个字段(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) (2)删除表中多余的重复记录(多个字段),只留有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)
把要保留的数拿到另外一张表 把原表删除 新表rename成老表
delete Inst_PlayerArenaRank from Inst_PlayerArenaRank where userId in (select userId from Inst_PlayerArenaRank group by userId having count(userId) > 1 ) and id not in (select id from Inst_PlayerArenaRank group by userId having count(userId)>1) 把这个帮忙改一下 大哥们
Inst_PlayerArenaRank表明 id自增长 userid用户名
查询用户名不重复的,并且id是最小的数据: select * from Inst_PlayerArenaRank where id in (select min(id) from user group by userid);删除的也很简单,删除用户名重复的,值保留id最小的: delete from Inst_PlayerArenaRank where id not in( select a.id from ( select min(id) as id from Inst_PlayerArenaRank group by userid ) a );
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) (2)删除表中多余的重复记录(多个字段),只留有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)
where userId in (select userId from Inst_PlayerArenaRank group by userId having count(userId) > 1 )
and id not in
(select id from Inst_PlayerArenaRank group by userId having count(userId)>1) 把这个帮忙改一下 大哥们
id自增长
userid用户名
select * from Inst_PlayerArenaRank where id in (select min(id) from user group by userid);删除的也很简单,删除用户名重复的,值保留id最小的:
delete from Inst_PlayerArenaRank where id not in(
select a.id from (
select min(id) as id from Inst_PlayerArenaRank group by userid
) a
);
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。