select * from yourtable group by 你的所有字段 having count(*)>1 这样就找到了你的重复记录了
--去重复记录处理 --1.如果表中有主键,可以直接这样删除 delete 表 from 表 a left join( select name,主键=min(主键) from 表 group by name )b on a.name=b.name and a.主键=b.主键 where b.主键 is null
--2.如果没有主键,建议增加一个标识字段来处理(这样才有效率)--添加标识字段 alter table 表 add 标识字段 decimal(38,0) identity(1,1) go--删除处理 delete 表 from 表 a left join( select name,标识字段=min(标识字段) from 表 group by name )b on a.name=b.name and a.标识字段=b.标识字段 where b.标识字段 is null go--完成后删除标识字段(不过我建议保留标识字段,表中没主键,很多处理都比较麻烦) alter table 表 drop column 标识字段
--3.如果你不愿意修改表结构,那就只好用临时表了(最低效的办法)--生成临时表 select 标识字段=identity(decimal(38,0),1,1),* into #t from 表 go--删除处理 delete #t from 表 a left join( select name,标识字段=min(标识字段) from #t group by name )b on a.name=b.name and a.标识字段=b.标识字段 where b.标识字段 is null go--删除处理完成后,删除临时表中的标识字段,方便数据导入 alter table #t drop column 标识字段 go--删除原表中的数据 truncate table 表 --这样速度够快,如果不能用这句,改为: delete 表 go--导回不重复的数据 insert into 表 select * from #t go--删除处理的临时表 drop table #t
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_qry] GO/*--查询重复记录的通用存储过程
可以查询出表中那些数据是重复的. 如果表中有主键,请指定主键. 如果表中有标识字段,而且标识字段无重复,请在调用时,将主键指定为标识字段 如果标识字段重复,不能用此存储过程--邹建 2004.2--*/create proc p_qry @tbname sysname, --要查询的表名 @keyfdname sysname=null --表中的主键,如果未指定,则表中无主键 as declare @nokey bit,@fd varchar(8000) set nocount on if isnull(@keyfdname,'')='' begin select @keyfdname=cast(newid() as char(36)),@nokey=1 exec('alter table ['+@tbname+'] add ['+@keyfdname+'] decimal(38,0) identity(1,1)') end set @fd='' select @fd=@fd+',['+name+']' from syscolumns where object_name(id)=@tbname and name<>@keyfdname set @fd=substring(@fd,2,8000) if @nokey=1 begin exec('select '+@fd+' from ['+@tbname+'] a join( select a=min(['+@keyfdname+']),b=max(['+@keyfdname+']) from ['+@tbname+'] group by '+@fd+' having count(*)>1 ) b on a.['+@keyfdname+'] between b.a and b.b') exec('alter table ['+@tbname+'] drop column ['+@keyfdname+']') end else exec('select a.* from ['+@tbname+'] a join( select a=min(['+@keyfdname+']),b=max(['+@keyfdname+']) from ['+@tbname+'] group by '+@fd+' having count(*)>1 ) b on a.['+@keyfdname+'] between b.a and b.b')set nocount off go--调用示例 --创建测试数据 create table 表(f1 int,f2 int,f3 int,f4 int,f5 int) insert into 表 select 1,1,1,1,1 union all select 2,1,1,1,1 union all select 3,2,1,23,1 union all select 4,2,3,1,3 go--调用通用存储过程实现楼主的查询 exec p_qry '表','f1'--删除测试环境 drop table 表 /*--测试结果f1 f2 f3 f4 f5 ----------- ----------- ----------- ----------- ----------- 1 1 1 1 1 2 1 1 1 1--*/
select distinct id,...(字段) into #temp from 表 where id in (select id from 表 group by id having count(id) >1) delete 表 where id in (select id from #temp group by id having count(id) >1) insert into 表 select * from #temp drop table #temp
參見:--刪除重復列 a.如果有ID字段,就是具有唯一性的字段delect table where id not in (select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用到判斷重复的字段b.,如果是判斷所有字段 select * into #aa from table group by id1,id2,.... delete table table insert into table select * from #aac.如果表中有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 子句后跟的字段就是你用到判斷重复的字段d. 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...)e. alter table yourtable add rownum int identity(1,1) go delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名) go alter table yourtable drop column rownum gof. 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 g. -- 刪除表中重復的記錄 DELETE delete1 FROM tabTest delete1 JOIN tabTest delete2 ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id
这样就找到了你的重复记录了
--1.如果表中有主键,可以直接这样删除
delete 表
from 表 a left join(
select name,主键=min(主键) from 表 group by name
)b on a.name=b.name and a.主键=b.主键
where b.主键 is null
--2.如果没有主键,建议增加一个标识字段来处理(这样才有效率)--添加标识字段
alter table 表 add 标识字段 decimal(38,0) identity(1,1)
go--删除处理
delete 表
from 表 a left join(
select name,标识字段=min(标识字段) from 表 group by name
)b on a.name=b.name and a.标识字段=b.标识字段
where b.标识字段 is null
go--完成后删除标识字段(不过我建议保留标识字段,表中没主键,很多处理都比较麻烦)
alter table 表 drop column 标识字段
--3.如果你不愿意修改表结构,那就只好用临时表了(最低效的办法)--生成临时表
select 标识字段=identity(decimal(38,0),1,1),* into #t from 表
go--删除处理
delete #t
from 表 a left join(
select name,标识字段=min(标识字段) from #t group by name
)b on a.name=b.name and a.标识字段=b.标识字段
where b.标识字段 is null
go--删除处理完成后,删除临时表中的标识字段,方便数据导入
alter table #t drop column 标识字段
go--删除原表中的数据
truncate table 表 --这样速度够快,如果不能用这句,改为: delete 表
go--导回不重复的数据
insert into 表 select * from #t
go--删除处理的临时表
drop table #t
drop procedure [dbo].[p_qry]
GO/*--查询重复记录的通用存储过程
可以查询出表中那些数据是重复的.
如果表中有主键,请指定主键.
如果表中有标识字段,而且标识字段无重复,请在调用时,将主键指定为标识字段
如果标识字段重复,不能用此存储过程--邹建 2004.2--*/create proc p_qry
@tbname sysname, --要查询的表名
@keyfdname sysname=null --表中的主键,如果未指定,则表中无主键
as
declare @nokey bit,@fd varchar(8000)
set nocount on
if isnull(@keyfdname,'')=''
begin
select @keyfdname=cast(newid() as char(36)),@nokey=1
exec('alter table ['+@tbname+'] add ['+@keyfdname+'] decimal(38,0) identity(1,1)')
end
set @fd=''
select @fd=@fd+',['+name+']' from syscolumns
where object_name(id)=@tbname and name<>@keyfdname
set @fd=substring(@fd,2,8000)
if @nokey=1
begin
exec('select '+@fd+' from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')
exec('alter table ['+@tbname+'] drop column ['+@keyfdname+']')
end
else
exec('select a.* from ['+@tbname+'] a join(
select a=min(['+@keyfdname+']),b=max(['+@keyfdname+'])
from ['+@tbname+'] group by '+@fd+'
having count(*)>1
) b on a.['+@keyfdname+'] between b.a and b.b')set nocount off
go--调用示例
--创建测试数据
create table 表(f1 int,f2 int,f3 int,f4 int,f5 int)
insert into 表
select 1,1,1,1,1
union all select 2,1,1,1,1
union all select 3,2,1,23,1
union all select 4,2,3,1,3
go--调用通用存储过程实现楼主的查询
exec p_qry '表','f1'--删除测试环境
drop table 表
/*--测试结果f1 f2 f3 f4 f5
----------- ----------- ----------- ----------- -----------
1 1 1 1 1
2 1 1 1 1--*/
where id in (select id from 表
group by id having count(id) >1)
delete 表
where id in (select id from #temp
group by id having count(id) >1)
insert into 表
select * from #temp
drop table #temp
a.如果有ID字段,就是具有唯一性的字段delect table where id not in (select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用到判斷重复的字段b.,如果是判斷所有字段
select * into #aa from table group by id1,id2,....
delete table table
insert into table
select * from #aac.如果表中有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 子句后跟的字段就是你用到判斷重复的字段d.
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...)e.
alter table yourtable add rownum int identity(1,1)
go
delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名)
go
alter table yourtable drop column rownum
gof.
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
g.
-- 刪除表中重復的記錄
DELETE delete1
FROM tabTest delete1
JOIN tabTest delete2
ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id