求sql句子 清理表内重复数据

[fansun]

有一表 a 字段如下

id username age
1 tom 25
2 jim 34
3 tim 65
4 jacky 33
5 jacky 33
6 tim 65



id 字段唯一 username里有重复信息 如何删除

[skyboy0720]

select min(id),username,age from 表 group by username,age

[futulove]

select id,username into #t from a group by username having count(*)>1
delete from (select * from a where username in(select username from #t)) a where a.id notin (select id from #t)
试试应该好使

[shanjimail888]

delete from a where username in (select username from a group by username having count(username )>=2)

[futulove]

TO:一楼
group by username,age 好像不行，你看给的数据他俩的都不一样

[futulove]

TO:三楼
你这样好像把所有重复的都给删除了，一个也没留吧

[futulove]

delete from a where id not in (select id from (select min(id)as id,username from a group by username ) b)
这个好使测试过

[chinaandys]

select * from taa
where id not in(select min(id) from taa group by username,age)

[yesterday2000]

select distinct *
into #a
from a
where username in
(select max(username)
from a
group by username
having (*)>1)

delete a from a a,#a b where a.username=b.username and a.age=b.age

insert into
a (usernaem,age)
select username,age from #a

[chenqinzeng]

删除重复的记录，只剩下一条
delete from table1
from (select max(c)as cc,a,b from table1 group by a,b having count(*) > 1) as table_1
where (table1.c <> table_1.cc) and (table1.a = table_1.a) and (table1.b = table_1.b)

[sunrisehy2003]

delete from a
where id not in (select min(id)as id ,username,age from a group by username,age)

[egxsun]

mark

[exingsa]

!!!up!!上面重复记录好像有只要删两条就可以了呀!!

[xikboy]

delete from a where id not in (select max(id) as id from a group by username)

[sankis]

Select Max(id), username Into #Table From table Group by username
Delete From table Where id not In (Select id #table)
Drop table #Table