删除重复数据一、具有主键的情况
a.具有唯一性的字段id(为唯一主键)
delete 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
delete #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
a.具有唯一性的字段id(为唯一主键)
delete 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
delete #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
2,select distinct * into 新表名 from 旧表名
删除旧表,再将新表名改成旧表名
--参考--删除重复的记录 create table t1(id int identity(0,1),a varchar(10),b varchar(10))
insert t1(a,b)
select 'aa','bb'
union all select 'a1','bgb'
union all select 'aa','bb'
union all select 'a2','bb'
union all select 'aa3','beeb'
union all select 'aa','bb'
union all select 'a2','bb'delete t1
where id not in
(select min(id) as id from t1 group by a,b)select * from t1drop table t1/*
id a b
------------------
0 aa bb
1 a1 bgb
3 a2 bb
4 aa3 beeb*/