mysql 删除表中的某列的重复字段

mysql 删除表中的某列的重复字段

参考：mysql 删除表中的某列的重复字段

建表

create table t(
id int not null primary key auto_increment,
name char(10) not null,
sex char(2) not null
)engine=Innodb;


insert into t values
(null,'tom','男'),
(null,'jack','男'),
(null,'小文','男'),
(null,'小文','女'),
(null,'tom','男'),
(null,'小张','男'),
(null,'小赵','女'),
(null,'tom','男'),
(null,'jack','男'),
(null,'小赵','女'),
(null,'haha','男');

删除重复字段SQL代码

先根据重复列的count(1)>1求出重复列，再跟原表对照，根据不会重复的id号，取出除第一个字段的id号以外的所有重复字段，最后进行删除

```
delete a.*
from t as a,
(select * from t group by name having count(1)>1) as b
where a.name=b.name
and a.id > b.id;/*a.id > b.id都是重复的*/
```

详细解释

1、首先把所有重复的第一个字段取出

select * from t group by name having count(1)>1

在SQL语句中count函数是最常用的函数之一，count函数是用来统计表中记录数的一个函数，

一. count(1)和count(*)的区别

	1. count(1)和count(*)的作用：

		都是检索表中所有记录行的数目，不论其是否包含null值。

	2. 区别：但是count(1)比count(*)效率更高

二 . count(字段)与count(1)和count(*)的区别

	count(字段)的作用是检索表中的这个字段的非空行数，不统计这个字段值为null的记录

2、再与原表组成对照表

select * from t as a,
(select * from t group by name having count(1)>1) as b
where a.name=b.name;

3、取出除第一个字段的id号以外的所有重复字段

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

4、进行删除

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