增加一个自动增长字段:
alter table table_name add IDFIELD integer identity(1,1)
,删除重复记录
delete from table_name a
where IDFIELD< (select max(IDFIELD) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);
最后执行
alter table table_name drop column IDFIELD 删除增加的字段。方法2:
创建临时表
select distinct * into tmp_table_name from table_name
然后,truncate table table_name
最后,insert into table_name select * from tmp_table_name
alter table table_name add IDFIELD integer identity(1,1)
,删除重复记录
delete from table_name a
where IDFIELD< (select max(IDFIELD) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);
最后执行
alter table table_name drop column IDFIELD 删除增加的字段。方法2:
创建临时表
select distinct * into tmp_table_name from table_name
然后,truncate table table_name
最后,insert into table_name select * from tmp_table_name
where m.a=n.a and m.b=n.b and m.c=n.c and m.d=n.d
那where里就可以不检查主键,那本来就是不能重复的。
select a from test group by a having count(*)>1
类推
having count(a)>1))and (b in (select b from test group by b
having count(b)>1))and (c in (select c from test group by c
having count(c)>1))and (d in (select d from test group by d
having count(d)>1))and
ALTER TABLE #TEMP ADD ID INT IDENTITY(1,1)SELECT * FROM #TEMP t
WHERE ID<(SELECT MAX(ID) FROM #TEMP
WHERE a=t.a AND b=t.b AND c=t.c AND d=t.d
GROUP BY a,b,c,d)DROP TABLE #TEMP
ALTER TABLE #TEMP ADD ID INT IDENTITY(1,1)
怎么个解释,洗耳恭听。。学习学习
那么另外是不是也可以直接用系统的inserted 表呢?