Menu
Woocommerce Menu

删除重复数据sql语句,(23522vip靠谱吗 行受影响)

0 Comment

(2 行受影响State of Qatar*///利用存款和储蓄进程

实例

exec p015
go
exec p016
go
exec p017
go

万一现成一张职员表(表名: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(name卡塔尔 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)

select distinct * into #tmp from tablenamedrop table tablenameselect
* into tablename from #tmpdrop table #tmp

方法三

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

alter table #t add id int identity–新扩大标记列godelete a from #t a
where exists(select 1 from #t where num=a.num and name=a.name and
ida.id卡塔尔–只保留一条记下goalter table #t drop column id–删除标记列

vieworder  id号      打卡机     成本种类    时间        价钱
1           a1       1号机     早餐       2006-08-03
07:09:23.000         2
2           a1       1号机     早餐       2006-08-03
07:10:13.000         2
3           a1       1号机     早餐       2006-08-03
07:10:19.000         2
4           a1       1号机     午餐       2006-08-03
12:02:10.000         5
5           a2       1号机     午餐       2006-08-03
12:11:10.000         5
6           a2       1号机     午餐       2006-08-03
12:12:10.000         5

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

方法1:

删去早先先用select语句查看要被删去的数量
select *
from 表 a
where exists(select * from 表 where  花费等级次序=a.花费档案的次序 and
时间>=dateadd(minute,-2,a.时间卡塔尔(قطر‎ and 时间<a.时间卡塔尔(قطر‎

–最轻便易行的求差总括
create procedure p017
as
begin
   select identity(int,1,1) id,qty into #tmp from a099
   declare cur_1 cursor local for
      select a.id,a.qty q1,b.qty q2 from #tmp a,#tmp b where
a.id=b.id-1
   declare @id int,@q1 int,@q2 int,@diff int
   declare @tbl table(id int identity(1,1),qty int)
   open cur_1
   fetch cur_1 into @id,@q1,@q2
   while @@fetch_status=0
   begin
                 select @diff=@q2-@q1
     insert into @tbl(qty) values(@diff)
     fetch cur_1 into @id,@q1,@q2
   end
   close cur_1
   select * from @tbl
end

declare roy_cursor cursor local forselect count(1)-1,num,name from
#t group by num,name having count(1)1declare @con int,@num int,@name
nvarchar(1)open roy_cursorfetch next from roy_cursor into
@con,@num,@namewhile @@fetch_status=0begin set rowcount @con; delete
#t where num=@num and name=@name set rowcount 0; fetch next from
roy_cursor into @con,@num,@nameendclose roy_cursordeallocate
roy_cursor

代码

exec up_010

–查看结果select * from #t

若是有重复的字段为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)

–alter table Orders drop constraint FK_Orders_Customers
3、计算数据库中各种客商表的记录数
    create proc up_011
as
begin
   create table #tmp (qty int)
   create table #tmp1 (tbl varchar(30),qty int)
        declare @sql varchar(99),@tbl varchar(30),@qty int
   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=\’select count(*) from \’+@tbl
      insert into #tmp exec(@SQL)
      select @qty=qty from #tmp
      insert into #tmp1 values(@tbl,@qty)
      delete from #tmp
      fetch cur_fks into @tbl
   end
   close cur_fks  
   select * from #tmp1
end

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图