贴: 删除重复数据一、具有主键的情况 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
--删除重复处理 delete a from 表 a left join( select 序号=min(序号) from 表 group by 编码) )b where a.序号=b.序号 where b.序号 is null
delect from yourtable t where exists(select * from yourtable where 编码=t.编码 and 序号<t.序号)
如果全重复用 select DISTINCT 序号,编码,名称 into #errkfmx from TB group by 序号,编码,名称 having count(*)>1delete tb from TB a,#errkfmx b where A.序号=B.序号 AND A.编码=B.编码 and a.名称=b.名称 insert into tb(序号,编码,名称) select 序号,编码,名称 from #errkfmx
测试: create table #t ( 序号 int identity(1,1), 编码 int, 名称 varchar(10) ) insert into #t select 1,'a' insert into #t select 1,'a' insert into #t select 1,'a' insert into #t select 2,'b' insert into #t select 3,'c' insert into #t select 3,'c' insert into #t select 3,'c' insert into #t select 4,'d' insert into #t select 3,'c' insert into #t select 4,'d' insert into #t select 4,'d' insert into #t select 5,'e'select * from #tdelete from #t where 序号 not in (select min(序号) as 序号 from #t group by 编码,名称) select * from #Tdrop table #t不好意思,我1楼写的有错误,应该是delete from yourtable where 序号 not in (select min(序号) as 序号 from yourtable group by 编码,名称)
delete a from 表 a left join(select 序号=min(序号) from 表 group by 编码))b where a.序号=b.序号 where b.序号 is null
一、具有主键的情况 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
to wanyingsong(豌豆) 是不是不应该再按 "名称"分组了?如果 编码与名称 一一对应 delete from yourtable where 序号 not in (select min(序号) as 序号 from yourtable group by 编码,名称) 否则就不该在用 "名称"分组了吧 delete from yourtable where 序号 not in (select min(序号) as 序号 from yourtable group by 编码)
是的如果编码和名称一一对应,就用group by 编码,名称 否则,按照楼主的意思,应该是group by 编码
delete select * from Table group by 号码 Having Count(*)>1
select distinct * into a from A delete from A insert A select * from a 这样也可以达到这个效果
--序號在數據庫中唯一(A) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1]create table temp1(序號 int not null ,編碼 varchar(20) not null,名稱 varchar(20) null,primary key (序號)) goinsert into temp1 select 1,'001','aaa' union select 2,'002','bbb' union select 3,'003','ccc' union select 4,'001','aaa' union select 5,'004','ddd' union select 6,'001','aaa' union select 7,'003','ccc' goselect * from temp1 /* 序號 編碼 名稱 1 001 aaa 2 002 bbb 3 003 ccc 4 001 aaa 5 004 ddd 6 001 aaa 7 003 ccc*/delete temp1 from temp1 a left join ( select max(序號) as 序號 from temp1 group by 編碼 ) b on a.序號=b.序號 where b.序號 is nullselect * from temp1 /* 序號 編碼 名稱 2 002 bbb 5 004 ddd 6 001 aaa 7 003 ccc*/ --序號在數據庫中不唯一,但序號+編碼在數據庫中唯一(B) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp2]create table temp2(序號 int not null ,編碼 varchar(20) not null,名稱 varchar(20) null,primary key (編碼,序號)) goinsert into temp2 select 1,'001','aaa' union select 1,'002','bbb' union select 1,'003','ccc' union select 2,'001','aaa' union select 1,'004','ddd' union select 3,'001','aaa' union select 2,'003','ccc' goselect * from temp2 /* 序號 編碼 名稱 1 001 aaa 2 001 aaa 3 001 aaa 1 002 bbb 1 003 ccc 2 003 ccc 1 004 ddd*/delete temp2 from temp2 a left join ( select 編碼,max(序號) as 序號 from temp2 group by 編碼 ) b on a.編碼=b.編碼 and a.序號=b.序號 where b.序號 is nullselect * from temp2 /* 序號 編碼 名稱 3 001 aaa 1 002 bbb 2 003 ccc 1 004 ddd*/ --序號在數據庫中不唯一,可能有多條完全相同記錄(C) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp3]create table temp3(序號 int not null ,編碼 varchar(20) not null,名稱 varchar(20) null) goinsert into temp3 select 1,'001','aaa' union all select 1,'002','bbb' union all select 1,'003','ccc' union all select 2,'001','aaa' union all select 1,'004','ddd' union all select 2,'001','aaa' union all select 2,'003','ccc' goselect * from temp3 /* 序號 編碼 名稱 1 001 aaa 1 002 bbb 1 003 ccc 2 001 aaa 1 004 ddd 2 001 aaa 2 003 ccc*/alter table temp3 add www int not null identity(1,1) godelete temp3 from temp3 a left join ( select max(www) as www from temp3 group by 編碼 ) b on a.www=b.www where b.www is nullalter table temp3 drop column www goselect * from temp3 /* 序號 編碼 名稱 1 002 bbb 1 004 ddd 2 001 aaa 2 003 ccc*/
删除重复数据一、具有主键的情况
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
--删除重复处理
delete a from 表 a left join(
select 序号=min(序号) from 表 group by 编码)
)b where a.序号=b.序号
where b.序号 is null
select DISTINCT 序号,编码,名称
into #errkfmx
from TB
group by 序号,编码,名称
having count(*)>1delete tb from TB a,#errkfmx b where A.序号=B.序号 AND A.编码=B.编码 and a.名称=b.名称
insert into tb(序号,编码,名称)
select
序号,编码,名称
from #errkfmx
create table #t
(
序号 int identity(1,1),
编码 int,
名称 varchar(10)
)
insert into #t select 1,'a'
insert into #t select 1,'a'
insert into #t select 1,'a'
insert into #t select 2,'b'
insert into #t select 3,'c'
insert into #t select 3,'c'
insert into #t select 3,'c'
insert into #t select 4,'d'
insert into #t select 3,'c'
insert into #t select 4,'d'
insert into #t select 4,'d'
insert into #t select 5,'e'select * from #tdelete from #t where 序号 not in (select min(序号) as 序号 from #t group by 编码,名称) select * from #Tdrop table #t不好意思,我1楼写的有错误,应该是delete from yourtable where 序号 not in (select min(序号) as 序号 from yourtable group by 编码,名称)
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
是不是不应该再按 "名称"分组了?如果 编码与名称 一一对应
delete from yourtable where 序号 not in (select min(序号) as 序号 from yourtable group by 编码,名称)
否则就不该在用 "名称"分组了吧
delete from yourtable where 序号 not in (select min(序号) as 序号 from yourtable group by 编码)
否则,按照楼主的意思,应该是group by 编码
select *
from Table
group by 号码
Having Count(*)>1
delete from A
insert A
select * from a
这样也可以达到这个效果
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp1]create table temp1(序號 int not null ,編碼 varchar(20) not null,名稱 varchar(20) null,primary key (序號))
goinsert into temp1
select 1,'001','aaa' union
select 2,'002','bbb' union
select 3,'003','ccc' union
select 4,'001','aaa' union
select 5,'004','ddd' union
select 6,'001','aaa' union
select 7,'003','ccc'
goselect * from temp1
/*
序號 編碼 名稱
1 001 aaa
2 002 bbb
3 003 ccc
4 001 aaa
5 004 ddd
6 001 aaa
7 003 ccc*/delete temp1 from temp1 a left join
(
select max(序號) as 序號 from temp1 group by 編碼
) b on a.序號=b.序號 where b.序號 is nullselect * from temp1
/*
序號 編碼 名稱
2 002 bbb
5 004 ddd
6 001 aaa
7 003 ccc*/
--序號在數據庫中不唯一,但序號+編碼在數據庫中唯一(B)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp2]create table temp2(序號 int not null ,編碼 varchar(20) not null,名稱 varchar(20) null,primary key (編碼,序號))
goinsert into temp2
select 1,'001','aaa' union
select 1,'002','bbb' union
select 1,'003','ccc' union
select 2,'001','aaa' union
select 1,'004','ddd' union
select 3,'001','aaa' union
select 2,'003','ccc'
goselect * from temp2
/*
序號 編碼 名稱
1 001 aaa
2 001 aaa
3 001 aaa
1 002 bbb
1 003 ccc
2 003 ccc
1 004 ddd*/delete temp2 from temp2 a left join
(
select 編碼,max(序號) as 序號 from temp2 group by 編碼
) b on a.編碼=b.編碼 and a.序號=b.序號 where b.序號 is nullselect * from temp2
/*
序號 編碼 名稱
3 001 aaa
1 002 bbb
2 003 ccc
1 004 ddd*/
--序號在數據庫中不唯一,可能有多條完全相同記錄(C)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp3]create table temp3(序號 int not null ,編碼 varchar(20) not null,名稱 varchar(20) null)
goinsert into temp3
select 1,'001','aaa' union all
select 1,'002','bbb' union all
select 1,'003','ccc' union all
select 2,'001','aaa' union all
select 1,'004','ddd' union all
select 2,'001','aaa' union all
select 2,'003','ccc'
goselect * from temp3
/*
序號 編碼 名稱
1 001 aaa
1 002 bbb
1 003 ccc
2 001 aaa
1 004 ddd
2 001 aaa
2 003 ccc*/alter table temp3 add www int not null identity(1,1)
godelete temp3 from temp3 a left join
(
select max(www) as www from temp3 group by 編碼
) b on a.www=b.www where b.www is nullalter table temp3 drop column www
goselect * from temp3
/*
序號 編碼 名稱
1 002 bbb
1 004 ddd
2 001 aaa
2 003 ccc*/