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) 试试应该好使
delete from a where username in (select username from a group by username having count(username )>=2)
TO:一楼 group by username,age 好像不行,你看给的数据他俩的都不一样
delete from a where id not in (select id from (select min(id)as id,username from a group by username ) b) 这个好使测试过
select * from taa where id not in(select min(id) from taa group by username,age)
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
删除重复的记录,只剩下一条 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)
delete from a where id not in (select min(id)as id ,username,age from a group by username,age)
delete from a where id not in (select max(id) as id from a group by username)
Select Max(id), username Into #Table From table Group by username Delete From table Where id not In (Select id #table) Drop table #Table
delete from (select * from a where username in(select username from #t)) a where a.id notin (select id from #t)
试试应该好使
group by username,age 好像不行,你看给的数据他俩的都不一样
这个好使测试过
where id not in(select min(id) from taa group by username,age)
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
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)
where id not in (select min(id)as id ,username,age from a group by username,age)
Delete From table Where id not In (Select id #table)
Drop table #Table