--或者:--备份不重复的数据 select distinct * into #t from 你的表 go--清除原表数据 truncate table 你的表/*--如果上面这句不能执行,改用: delete from 你的表 --*/ go--还原数据 insert into 你的表 select * from #t go--删除临时表 drop table #t
蚂蚁的:去除重复值 如果有ID字段,就是具有唯一性的字段delect table where id not in ( select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,如果是判断所有字段也可以这样 select * into #aa from table group by id1,id2,.... delete table insert into table select * from #aa3,没有ID的情况select identity(int,1,1) as id,* into #temp from tabel delect # where id not in ( select max(id) from # group by col1,col2,col3...) delect table inset into table(...) select ..... from #temp 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字段内容相同即表示记录相同。2, select identity(int,1,1) as id,* into #temp from tabel select * from #temp where id in ( select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
还是建议用truncate table,然后重新导入的方法。
这样可以么? table2 1 as 3 g 4 k 5 g 1 as 下面是sql select distinct * into temptable1 from table2 truncate table table2 insert into table2 select * from temptable1 drop table temptable1 select * from table2----------测试结果------------- 1 as 3 g 4 k 5 g --------------------------------(所影响的行数为 4 行) (所影响的行数为 4 行) (所影响的行数为 4 行)
把重复的记录中的一条插入临时表# 清空你的表 从临时表中导入. 删除临时表. select distinct * into # from tablename truncate table tablename select * into tablename from # drop table #
select distinct * into #t from 你的表
go--清除原表数据
truncate table 你的表/*--如果上面这句不能执行,改用:
delete from 你的表
--*/
go--还原数据
insert into 你的表 select * from #t
go--删除临时表
drop table #t
蚂蚁的:去除重复值
如果有ID字段,就是具有唯一性的字段delect table where id not in ( select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa3,没有ID的情况select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp
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字段内容相同即表示记录相同。2,
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
table2
1 as
3 g
4 k
5 g
1 as
下面是sql
select distinct * into temptable1 from table2
truncate table table2
insert into table2 select * from temptable1
drop table temptable1
select * from table2----------测试结果-------------
1 as
3 g
4 k
5 g
--------------------------------(所影响的行数为 4 行)
(所影响的行数为 4 行)
(所影响的行数为 4 行)
清空你的表
从临时表中导入.
删除临时表.
select distinct * into # from tablename
truncate table tablename
select * into tablename from #
drop table #