如果有id delete from tablename a where exists(select * from tablename where a.id<id and a.col1=col1) 如果没有id alter table tablename add id int identity(1,1) delete from tablename a where exists(select * from tablename where a.id<id and a.col1=col1) alter table drop cloumn id
JafyLiu(土豆) ( ) 的不对,那不是把所有重复的记录都删除了!!!
alter table tablename add id int identity(1,1)delete from tablename a where exists (select * from tablename where a.id=id and a.col1=col1 and a.col1 in (select col1 from tablename group by col1 having count(*)>1) alter table drop cloumn id
在表中添加一个自动ID,然后根据自动ID来进行删除
同意 cocosoft(pengyun)的说法!!
使用distinct和临时表if object_id('tempdb..#tmp') is not null drop table #tmp select distinct * into #tmp from tt truncate table tt insert into tt select * from #tmp
如何查询table中有重复记录例子:select *,count(1) as rownum from tb1 group by f1, f2 having count(1) > 1
select id=identity(int,1,1),* into #tmp from 表 delete * from #tmp where id in(select min(id) from #tmp group by col1) drop table 表 sp_rename '#tmp','表' alter table 表 drop column id select * from 表 go
给表加上个id delete from tablename where id not in (select min(id) from tablename group by id having count(*)>1) and id not in(select min(id) from tablename group by id having count(*)=1)
Sorry,搞错 select id=identity(int,1,1),* into #tmp from 表 delete * from #tmp where id not in(select min(id) from #tmp group by col1) drop table 表 sp_rename '#tmp','表' alter table 表 drop column id select * from 表 go
delete from table where id in ( select distinct id from table group by id having count(id) > 1) --------------------------------------------------------------------- JafyLiu(土豆) ( ) 的不对,那不是把所有重复的记录都删除了!!! --------------------------------------------------------------------- 不是不对是需要在外面加个循环,条件为 select distinct id from table group by id having count(id) > 1 返回的值为空,即没有重复的为止。
select distinct * into #1 from 表 delete from 表 insert into 表 select * from #1
select distinct * into #1 from 表 delete from 表 insert into 表 select * from #1
delete from tablename a where exists(select * from tablename where a.id<id and a.col1=col1)
如果没有id
alter table tablename add id int identity(1,1)
delete from tablename a where exists(select * from tablename where a.id<id and a.col1=col1)
alter table drop cloumn id
alter table drop cloumn id
drop table #tmp
select distinct * into #tmp from tt
truncate table tt
insert into tt select * from #tmp
from tb1
group by f1, f2
having count(1) > 1
delete * from #tmp
where id in(select min(id) from #tmp group by col1)
drop table 表
sp_rename '#tmp','表'
alter table 表
drop column id
select * from 表
go
這裏有很幾個例子,挺全面的.
delete from tablename where id not in (select min(id) from tablename group by id having count(*)>1) and id not in(select min(id) from tablename group by id having count(*)=1)
select id=identity(int,1,1),* into #tmp from 表
delete * from #tmp
where id not in(select min(id) from #tmp group by col1)
drop table 表
sp_rename '#tmp','表'
alter table 表
drop column id
select * from 表
go
( select distinct id from table group by id having count(id) > 1)
---------------------------------------------------------------------
JafyLiu(土豆) ( ) 的不对,那不是把所有重复的记录都删除了!!!
---------------------------------------------------------------------
不是不对是需要在外面加个循环,条件为 select distinct id from table group by id having count(id) > 1 返回的值为空,即没有重复的为止。
delete from 表
insert into 表 select * from #1
delete from 表
insert into 表 select * from #1