删除重复数据sql语句,(2 行受影响)

(2 行受影响),假设有重复的字段为name,要求得到这两个字段唯一的结果集,begin

sql 删除重复记录未有高低关系时,管理重复值

假如有重复的字段为name,address,须要取得这四个字段独一的结果集
select identity(int,1,1) as autoid, * into #tmp from tablename
select min(autoid) as autoid into #tmp2 from #tmp group by
name,autoid
select * from #tmp where autoid in(select autoid from #tmp2)

select * from a099
select * from a099 a,a099 b where a.id=b.id

设若现存一张职员表(表名:Person),若想将姓名、身份ID号、住址那多个字段完全相像的记录查寻找来,使用

   1:  SELECT p1.*   

   2:  FROM persons   p1,persons   p2   

   3:  WHERE p1.id<>p2.id   

   4:  AND p1.cardid   =   p2.cardid   

   5:  AND p1.pname   =   p2.pname   

   6:  AND p1.address   =   p2.address

能够完结该成效。

删去重复记录的SQL语句

1.用rowid方法

2.用group by方法

3.用distinct方法

1。用rowid方法

据据oracle带的rowid属性,进行判断,是或不是留存重新,语句如下:
查数据:      select * from table1 a where rowid !=(select   max(rowid)  
     from table1 b where a.name1=b.name1 and a.name2=b.name2……)
删数据:
    delete   from table1 a where rowid !=(select   max(rowid)  
     from table1 b where a.name1=b.name1 and a.name2=b.name2……)

2.group by方法

查数据:
select count(numState of Qatar, max(nameState of Qatar from student
–列出双重的记录数,并列出她的name属性
group by num
having count(num卡塔尔国 >1
–按num分组后寻找表中num列重复,即现身次数当先一遍
删数据:
delete from student
group by num
having count(num) >1
那样的话就把具有重复的都剔除了。

3.用distinct方法 -对于小的表比较有用

create table table_new as   select distinct *   from table1 minux
truncate table table1;
insert into table1 select * from table_new;

查询及删除重复记录的不二等秘书技大全
1、查找表中多余的重复记录,重复记录是基于单个字段(peopleId)来剖断

select * from people
where peopleId in (select  peopleId  from  people  group  by 
peopleId  having  count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是依照单个字段(peopleId)来判别,只留有rowid最小的笔录
delete from people
where peopleId  in (select  peopleId  from people  group  by 
peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId 
having count(peopleId )>1)

3、查找表中多余的重复记录(八个字段)
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by
peopleId,seq  having count(*) > 1)

4、删除表中多余的重复记录(多少个字段),只留有rowid最小的笔录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq
having count(*)>1)

5、查找表中剩下的重复记录(多个字段),不带有rowid最小的笔录
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq
having count(*)>1)

(二卡塔尔 比方说 在A表中设有三个字段“name”,
还要不一样记录之间的“name”值有非常的大概率会周围,
明日就是急需查询出在该表中的各记录之间,“name”值存在双重的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1

一旦还查性别也一致大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*)
> 1

(三) 方法一

declare @max integer,@id integer

declare cur_rows cursor local for select 主字段,count(*) from 表名
group by 主字段 having count(*) >; 1

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

delete from 表名 where 主字段 = @id

fetch cur_rows into @id,@max

end

close cur_rows

set rowcount 0

方法二

"重复记录"有多少个意思上的重复记录,一是一点一滴重复的笔录,也即具备字段均再一次的记录,二是有的最主要字段重复的笔录,例如Name字段重复,而其余字段不自然再一次或都再一次能够忽视。

1、对于第一种重复,比较便于解决,使用

select distinct * from tableName

就足以拿走无重复记录的结果集。

若果该表供给删除重复的笔录(重复记录保留1条),能够按以下方法删除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

发生这种重新的原因是表设计不周发生的,扩大独一索引列就可以解决。

2、这类重复难点普通要求保存重复记录中的第一条记下,操作方法如下

要是有重复的字段为Name,Address,必要得到那三个字段独一的结果集

select identity(int,1,1) as autoID, * into #Tmp from tableName

select min(autoID) as autoID into #Tmp2 from #Tmp group by
Name,autoID

select * from #Tmp where autoID in(select autoID from #tmp2)

谈到底四个select即获得了Name,Address不另行的结果集(但多了三个autoID字段,实际写时得以写在select子句中节约此列)

(四卡塔尔国 查询重复

select * from tablename where id in (

select id from tablename

group by id

having count(id) > 1)

(2 行受影响卡塔尔*/

实例

exec p015
go
exec p016
go
exec p017
go

//使用函数

删除重复数据sql语句
方法一

–轻松的求差难题
alter proc p016
as
begin
  declare cur_1 cursor local for select * from a099 order by 1
        declare @id int,@qty int,
          @id1 int,@qty1 int,@diff int
  declare @tbl table(id int identity(1,1),qty int)
  open cur_1
  fetch cur_1 into @id ,@qty
  while @@fetch_status=0
  begin
    if @qty1<>0
    begin
      select @diff=@qty1-@qty
      select @qty=@qty1
      insert into @tbl(qty) values(@diff)
    end
    fetch cur_1 into @id1,@qty1
  end
  close cur_1  
  select * from @tbl
end

–查看结果select * from #t/*num name———– —-1 a2 b

方法三

exec up_008 a_idx2
2、编制程序删除数据库中的顾客表
create proc up_010
as
begin
        declare @sql varchar(99),@tbl varchar(30),@fk varchar(30)
   declare cur_fk cursor local for
      select object_name(constid),object_name(fkeyid) from
sysreferences
   –删除全部外键
   open cur_fk
   fetch cur_fk into @fk,@tbl
   while @@fetch_status =0
   begin
                select @sql=\’alter table \’+@tbl+\’ drop constraint
\’+@fk
      exec(@SQL)
      –select @sql=\’drop table \’+@tbl
      fetch cur_fk into @fk,@tbl
   end
   close cur_fk
   declare cur_fks cursor local for
      select name from sysobjects where xtype=\’U\’
   open cur_fks
   fetch cur_fks into @tbl
   while @@fetch_status =0
   begin
      select @sql=\’drop table [\’+@tbl+\’]\’
      exec(@SQL)
      fetch cur_fks into @tbl
   end
   close cur_fks  
end

/*num name———– —-1 a2 b

delete from 表 a
where exists(select * from 表 where  花费项目=a.花销项目 and
时间>=dateadd(minute,-2,a.时间卡塔尔国 and 时间<a.时间卡塔尔

select * from a099 a,a099 b where a.id=b.id

–重新执行测量检验数据后用方法3情势3:

方法一
即便有重复的字段为name,address,必要获得这八个字段独一的结果集 select
identity(int,1,1卡塔尔 as autoid, * into #tmp from t…

create table #tmp(qty int)
insert into #tmp exec(\’select count(*) from sales\’)
select * from #tmp