alter table 表 add newfield int identity(1,1)delete 表 where newfield not in( select min(newfield) from 表 group by 除newfield外的所有字段 )alter table 表 drop column newfield或:select * into #temp from 表 group by 所有列 truncate table 表 insert 表 select * from #temp drop table #temp
select distinct * into #temp from 表 truncate table 表 insert 表 select * from #temp drop table #temp
非常感谢!能就我上面举的table1,a,b,c的例子写出具体的语句吗?
select distinct * into #temp from table1 truncate table table1 insert table1 select * from #temp drop table #temp
你用这个吧: alter table table1 add newfield int identity(1,1)delete table1 where newfield not in(select min(newfield) from table1 group by a,b,c)alter table table1 drop column newfield
对于text,ntext,或image的数据类型的字段不能用distinct! 所以你的只能用上述大力的方法 alter table table1 add tempID int identity(1,1)delete table1 where tempID not in ( select min(tempID) from table1 group by a,b,c ) alter table table1 drop column tempID
删除重复数据一、具有主键的情况 a.具有唯一性的字段id(为唯一主键) delect table where id not in ( select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用来判断重复的条件,如只有col1, 那么只要col1字段内容相同即表示记录相同。b.具有联合主键 假设col1+','+col2+','...col5 为联合主键 select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table where having count(*)>1 group by col1,col2,col3,col4 ) group by 子句后跟的字段就是你用来判断重复的条件, 如只有col1,那么只要col1字段内容相同即表示记录相同。c:判断所有的字段 select * into #aa from table group by id1,id2,.... delete table insert into table select * from #aa二、没有主键的情况a:用临时表实现 select identity(int,1,1) as id,* into #temp from ta delect #temp where id not in ( select max(id) from # group by col1,col2,col3... ) delete table ta inset into ta(...) select ..... from #tempb:用改变表结构(加一个唯一字段)来实现 alter table 表 add newfield int identity(1,1) delete 表 where newfield not in ( select min(newfield) from 表 group by 除newfield外的所有字段 )alter table 表 drop column newfield
where newfield not in(
select min(newfield) from 表 group by 除newfield外的所有字段
)alter table 表 drop column newfield或:select * into #temp from 表 group by 所有列
truncate table 表
insert 表 select * from #temp
drop table #temp
truncate table 表
insert 表 select * from #temp
drop table #temp
truncate table table1
insert table1 select * from #temp
drop table #temp
alter table table1 add newfield int identity(1,1)delete table1 where newfield not in(select min(newfield) from table1 group by a,b,c)alter table table1 drop column newfield
所以你的只能用上述大力的方法
alter table table1 add tempID int identity(1,1)delete table1
where tempID not in
(
select min(tempID)
from table1
group by a,b,c
)
alter table table1 drop column tempID
a.具有唯一性的字段id(为唯一主键)
delect table
where id not in
(
select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,
那么只要col1字段内容相同即表示记录相同。b.具有联合主键
假设col1+','+col2+','...col5 为联合主键
select * from table where col1+','+col2+','...col5 in (
select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,
如只有col1,那么只要col1字段内容相同即表示记录相同。c:判断所有的字段
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa二、没有主键的情况a:用临时表实现
select identity(int,1,1) as id,* into #temp from ta
delect #temp
where id not in
(
select max(id) from # group by col1,col2,col3...
)
delete table ta
inset into ta(...)
select ..... from #tempb:用改变表结构(加一个唯一字段)来实现
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in
(
select min(newfield) from 表 group by 除newfield外的所有字段
)alter table 表 drop column newfield