select identity(1,1) as ID, name, sex, no, add, phone, zip into #temp from 总表delete from #temp where ID not in(select min(ID) from #temp group by name, add)truncate table 总表insert into 总表 select name, sex, no, add, phone, zip from #temp order by IDdrop table #temp
或者如下操作:1、将数据导入具有自增字段ID的临时表 #temp select identity(1,1) as ID, name, sex, no, add, phone, zip into #temp from 总表2、清空原始表 truncate table 总表3、将name和add相同的记录中ID最小的那条记录插回原始表 insert into 总表 select name, sex, no, add, phone, zip from #temp where ID in(select min(ID) from #temp group by name, add) order by ID4、删除临时表 drop table #temp
如果name,add相同的纪录的其他字段有一个保证不一样,那很简单delete from table1 where exists (select 1 from table1 b where table1.name=b.name and table1.add=b.add and table1.other<>b.other)
假设总表名为:table_abegin tran t1 select identity(int,1,1) as id,name,sex,no,add,phone,zip into table_bak from table_adelete a from table_bak as a where exists (select * from table_bak where name=a.name and add=a.add and id<a.id) truncate table table_ainsert table_a(name,sex,no,add,phone,zip) select name,sex,no,add,phone,zip from table_bak drop table table_bak if @@error=0 commit tran t1 else rollback tran t1
改一下delete from table1 where exists (select 1 from table1 b where table1.name=b.name and table1.add=b.add and table1.other<b.other)如果存在两条完全一样的纪录,那么需要给table加个自增的字段alter table table1 add NewID int identity(1,1)然后delete from table1 where exists (select 1 from table1 b where table1.name=b.name and table1.add=b.add and table1.NewID<b.NewID)然后 alter table table1 drop column NewID
alter table table1 add NewID int identity(int,1,1)delete from table1 where exists (select 1 from table1 b where table1.name=b.name and table1.add=b.add and table1.NewID<b.NewID)alter table table1 drop column NewID
比如
aa,cc,dd
aa,cc,bb
cc,aa,bb
dd,aa,cc要得到的效果是 aa,cc,dd
cc,aa,bb
dd,aa,cc
一行代表一条记录
aa,cc,bb===>aa,cc,dd
有没有时间字段来区分有先后的呢?要不就很难知道你要保留的dd
select identity(1,1) as ID, name, sex, no, add, phone, zip into #temp from 总表2、清空原始表
truncate table 总表3、将name和add相同的记录中ID最小的那条记录插回原始表
insert into
总表
select
name, sex, no, add, phone, zip
from
#temp
where
ID in(select min(ID) from #temp group by name, add)
order by
ID4、删除临时表
drop table #temp
假设总表名为:table_abegin tran t1
select identity(int,1,1) as id,name,sex,no,add,phone,zip into table_bak from table_adelete a from table_bak as a where exists (select * from table_bak where name=a.name and add=a.add and id<a.id)
truncate table table_ainsert table_a(name,sex,no,add,phone,zip)
select name,sex,no,add,phone,zip from table_bak
drop table table_bak
if @@error=0
commit tran t1
else
rollback tran t1
alter table table1 drop column NewID
我这里是用access操作的,做个报表用的.
宁哥这方法不错.
不过这样删除都是比较慢.是否exists的问题.
也谢谢别的各位热心人咯.
通过group by 条件
having 条件
得以实现!