alter table tb add id int identity(1,1)delete from tb where id!=1
delete tb where exists (select 1 from tb t where t.col1=tb.col1 and t.col2=tb.col2 and ... and t.GUID<tb.GUID)
delete from tb where guid in (select max(guid) from tb group by 列...)
select distinct * into #temp from tbl drop table tbl select * into tbl from #temp drop table #temp
写错,应该 delete from tb where guid not in (select max(guid) from tb group by 列...)
删除要谨慎……我先把Delete都改成Select试试,呵呵
这个不行,我Select了一下,执行了7分钟,没有找到结果
[code=SQL] --增加一个自增量的临时列TID alter table [tablename] add [TID] int IDENTITY(1,1) --删除重复记录 delete from [tablename] where TID not in (select max(TID) from [tablename] group by ...) --删除临时列TID alter table [tablename] drop column [TID]
--增加一个自增量的临时列TID alter table [tablename] add [TID] int IDENTITY(1,1) --删除重复记录 delete from [tablename] where TID not in (select max(TID) from [tablename] group by ...) --删除临时列TID alter table [tablename] drop column [TID]
可以利用SQL 2005中的ROW_NUMBER() PARTITION BY语句来搞定,这也是效率很高的一种方式:delete from a from (select ROW_NUMBER() over ( partition by 重复列1, 重复列2, 重复列3 --,重复列n order by 重复列1, 重复列2, 重复列3 --,重复列n ) RowNumber from 表名) a where a.RowNumber > 1
drop table tbl
select * into tbl from #temp
drop table #temp
delete from tb where guid not in (select max(guid) from tb group by 列...)
--增加一个自增量的临时列TID
alter table [tablename] add [TID] int IDENTITY(1,1) --删除重复记录
delete from [tablename] where TID not in
(select max(TID) from [tablename] group by ...) --删除临时列TID
alter table [tablename] drop column [TID]
--增加一个自增量的临时列TID
alter table [tablename] add [TID] int IDENTITY(1,1) --删除重复记录
delete from [tablename] where TID not in
(select max(TID) from [tablename] group by ...) --删除临时列TID
alter table [tablename] drop column [TID]
from
(select ROW_NUMBER() over
(
partition by 重复列1, 重复列2, 重复列3 --,重复列n
order by 重复列1, 重复列2, 重复列3 --,重复列n
) RowNumber
from 表名) a
where a.RowNumber > 1