3522vip-澳门新葡亰平台官网-www.3522vip.com

3522vip☞(www.rivieraquest.com)能够为大家带来真正的真钱享受,澳门新葡亰平台官网开创业内先河,注册,开户,登录开始体验不同的娱乐世界,全国第一家以娱乐产品为主体对象的专业平台,菲律宾全资子公司成立,天天免费68周周再送168。

3522vip > 网络数据库 > mysql删除重复数据记录sql语句总结

原标题:mysql删除重复数据记录sql语句总结

浏览次数:172 时间:2019-11-23

借助临时表

例如: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id是主键 要求得到这样的结果 id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii

drop table t_user;
create table t_user(
id        int(5) not null auto_increment,
username varchar(10),
age       int(3),
primary key(id)
);

一. 删除完全重复的记录

方法1复制代码 代码如下:delete YourTable where [id] not in from YourTable group by 方法2复制代码 代码如下:delete a from 表 a left join from 表 group by name,value )b on a.id=b.id where b.id is null

select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as  bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid where  a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid

完全重复的数据,通常是由于没有设置主键/唯一键约束导致的。测试数据:复制代码 代码如下:if OBJECT_ID is not nulldrop table duplicate_all GO create table duplicate_all ( c1 int, c2 int, c3 varchar GO insert into duplicate_all select 1,100,'aaa' union allselect 1,100,'aaa' union allselect 1,100,'aaa' union allselect 1,100,'aaa' union allselect 1,100,'aaa' union allselect 2,200,'bbb' union allselect 3,300,'ccc' union allselect 4,400,'ddd' union allselect 5,500,'eee'GO

查询及删除重复记录的SQL语句查询及删除重复记录的SQL语句1、查找表中多余的重复记录,重复记录是根据单个字段来判断复制代码 代码如下:select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count2、删除表中多余的重复记录,重复记录是根据单个字段来判断,只留有rowid最小的记录复制代码 代码如下:delete from peoplewhere peopleId in (select peopleId from people group by peopleId having countand rowid not in from people group by peopleId having count3、查找表中多余的重复记录复制代码 代码如下:select * from vitae awhere in (select peopleId,seq from vitae group by peopleId,seq having count4、删除表中多余的重复记录,只留有rowid最小的记录复制代码 代码如下:delete from vitae awhere in (select peopleId,seq from vitae group by peopleId,seq having countand rowid not in from vitae group by peopleId,seq having count5、查找表中多余的重复记录,不包含rowid最小的记录复制代码 代码如下:select * from vitae awhere in (select peopleId,seq from vitae group by peopleId,seq having countand rowid not in from vitae group by peopleId,seq having count比方说在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方法一复制代码 代码如下:declare @max integer,@id integerdeclare cur_rows cursor local for select 主字段,count from 表名 group by 主字段 having count >; 1open cur_rowsfetch cur_rows into @id,@maxwhile @@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete from 表名 where 主字段 = @idfetch cur_rows into @id,@maxendclose cur_rowsset rowcount 0方法二"重复记录"有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。 1、对于第一种重复,比较容易解决,使用select distinct * from tableName 就可以得到无重复记录的结果集。 如果该表需要删除重复的记录,可以按以下方法删除复制代码 代码如下:select distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp 发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。 2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下 假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集复制代码 代码如下:select identity as autoID, * into #Tmp from tableNameselect min as autoID into #Tmp2 from #Tmp group by Name,autoIDselect * from #Tmp where autoID in(select autoID from #tmp2) 最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)查询重复复制代码 代码如下:select * from tablename where id in (select id from tablenamegroup by idhaving count

复制代码

如果多个表有完全重复的行,可以考虑通过UNION将多个表联合,插到一个新的同结构的表,SQL Server会帮助去掉表和表之间的重复行。

  1. 查询需要删除的记录,会保留一条记录。

唯一索引有个忽略重复建的选项,在创建主键约束/唯一键约束时都可以使用这个索引选项。复制代码 代码如下:if OBJECT_ID is not nulldrop table tmp GO create table tmp ( c1 int, c2 int, c3 varchar, constraint UQ_01 unique with ) GO insert into tmp select * from duplicate_col select * from tmp 借助主键/唯一键来删除通常会选择主键/唯一键的最大/最小值保留,其他行删除。以下只保留重复记录中c1最小的行。复制代码 代码如下:delete from duplicate_col where exists(select 1 from duplicate_col b where duplicate_col.c1 > b.c1 and (duplicate_col.c2 = b.c2 and duplicate_col.c3 = b.c3)) --或者 复制代码 代码如下:delete from duplicate_col where c1 not in from duplicate_col group by c2,c3)如果要保留重复记录中的第N行,可以参考05.取分组中的某几行。 ROW_NUMBER和删除完全重复记录的写法基本一样。复制代码 代码如下:with tmp as( select *,ROW_NUMBER() OVER(PARTITION BY c2,c3 ORDER BY as num from duplicate_col ) delete tmp where num > 1 select * from duplicate_colSQL删除重复数据只保留一条 用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢1、查找表中多余的重复记录,重复记录是根据单个字段来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count 2、删除表中多余的重复记录,重复记录是根据单个字段来判断,只留有rowid最小的记录 delete from people where peopleName in (select peopleName from people group by peopleName having count and peopleId not in from people group by peopleName having count 3、查找表中多余的重复记录 select * from vitae a where in (select peopleId,seq from vitae group by peopleId,seq having count 4、删除表中多余的重复记录,只留有rowid最小的记录 delete from vitae a where in (select peopleId,seq from vitae group by peopleId,seq having count and rowid not in from vitae group by peopleId,seq having count 5、查找表中多余的重复记录,不包含rowid最小的记录 select * from vitae a where in (select peopleId,seq from vitae group by peopleId,seq having count and rowid not in from vitae group by peopleId,seq having count 6.消除一个字段的左边的第一位:update tableName set [Title]=Right-1)) where Title like '村%'7.消除一个字段的右边的第一位:update tableName set [Title]=left-1)) where Title like '%村'8.假删除表中多余的重复记录,不包含rowid最小的记录 update vitae set ispass=-1where peopleId in (select peopleId from vitae group by peopleId

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)

部分列重复的数据,通常表上是有主键的,可能是程序逻辑造成了多行数据列值的重复。测试数据:复制代码 代码如下:if OBJECT_ID is not nulldrop table duplicate_col GO create table duplicate_col ( c1 int primary key, c2 int, c3 varchar GO insert into duplicate_col select 1,100,'aaa' union allselect 2,100,'aaa' union allselect 3,100,'aaa' union allselect 4,100,'aaa' union allselect 5,500,'eee'GO

...

利用DISTINCT得到单条记录,删除源数据,然后导回不重复记录。如果表不大的话,可以把所有记录导出一次,然后truncate表后再导回,这样可以避免delete的日志操作。复制代码 代码如下:if OBJECT_ID is not nulldrop table #tmp GO select distinct * into #tmp from duplicate_all where c1 = 1 GO delete duplicate_all where c1 = 1 GO insert into duplicate_all select * from #tmp 使用ROW_NUMBER复制代码 代码如下:with tmp as( select *,ROW_NUMBER() OVER(PARTITION BY c1,c2,c3 ORDER BY as num from duplicate_all where c1 = 1 ) delete tmp where num > 1

  1. 删除重复记录,只保留一条记录。注意,subject,RECEIVER 要索引,否则会很慢的。

唯一索引

4. 删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

二. 删除部分重复的记录

delete tb_person as a from tb_person as a,
(
select *,min(id) from tb_person  group by name having count(1) > 1
) as b
 where a.name = b.name and a.id > b.id;

复制代码

  1. 查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断

复制代码

delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as  bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;

 代码如下

mysql> delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id;
Query OK, 4 rows affected (0.05 sec)

好了下面再总结一些

5.删除表中多余的重复记录(多个字段),只留有rowid最小的记录

复制代码

mysql> select * from t_user;
---- ---------- ------
| id | username | age |
---- ---------- ------
| 1 | aaa      |   20 |
| 2 | aaa      |   20 |
| 3 | bbb      |   20 |
| 4 | bbb      |   20 |
| 5 | ccc      |   20 |
| 6 | ccc      |   20 |
| 7 | ddd      |   20 |
| 8 | ddd      |   20 |
---- ---------- ------

复制代码

 代码如下

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)

//删除id重复的数据,适合id是手工主键
delete person as a from person as a,
(
    select *,min(id) from person group by id having count(1) > 1
) as b
where a.id = b.id

复制代码

例子如下:

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

复制代码

说了这么多了我们来个例子吧

我最常用的方法是

 代码如下

复制代码

 代码如下

 代码如下

 代码如下

 代码如下

//查找重复的,并且除掉最小的那个

 代码如下

删除重复记录的方法有很多种,这里我来总结了各种各样的删除重启记录的sql语句,如:删除id重复的数据,查找重复的,并且除掉最小的那个,删除重复记录,只保留一条记录和删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录等等

mysql> select * from t_user;
---- ---------- ------
| id | username | age |
---- ---------- ------
| 2 | aaa      |   20 |
| 4 | bbb      |   20 |
| 6 | ccc      |   20 |
| 8 | ddd      |   20 |
---- ---------- ------

insert into t_user(username,age) values('aaa',20);
insert into t_user(username,age) values('aaa',20);
insert into t_user(username,age) values('bbb',20);
insert into t_user(username,age) values('bbb',20);
insert into t_user(username,age) values('ccc',20);
insert into t_user(username,age) values('ccc',20);
insert into t_user(username,age) values('ddd',20);
insert into t_user(username,age) values('ddd',20);

本文由3522vip发布于网络数据库,转载请注明出处:mysql删除重复数据记录sql语句总结

关键词: 3522vip

上一篇:澳门新葡亰平台官网SQL Server中删除重复数据的几

下一篇:没有了