mysql> select * from tet7;
+----+------+
| id | t1 |
+----+------+
| 1 | 001 |
| 2 | 001 |
| 3 | 002 |
| 4 | 003 |
| 5 | 003 |
+----+------+
5 rows in set (0.00 sec)
删除重复值:
mysql> delete from tet7 where id in (select a.id from (select max(id) as id from tet7 group by t1 having count(*)>1) a);
Query OK, 2 rows affected (0.06 sec)
ps:这个sql好理解,就是取出重复最大id值,然后根据这个id值删除该行数据... 注意,在子查询中,一定要将查出来的最大id,单独别名,在select这个别名表,否则会报语法错误.
以上是针对重复只有一个的..也就是相同的数据只有2行的情况..对于相同的行有多行的,有两种sql:
1.
mysql> select * from tet6;
+----+------+---------+
| id | d1 | d2 |
+----+------+---------+
| 1 | 1 | peng |
| 2 | 1 | peng |
| 3 | 2 | te |
| 4 | 3 | te |
| 5 | 3 | te |
| 6 | 3 | te |
| 7 | 4 | te |
+----+------+---------+
7 rows in set (0.00 sec) mysql> delete a
-> from tet6 as a,
-> (
-> select d1,d2,max(id) tt from tet6 group by d1,d2 having count(*) >1
-> ) as b
-> where a.id <b.tt and a.d1=b.d1 and a.d2=b.d2;
Query OK, 3 rows affected (0.04 sec)
ps:sql将原表作为a,将重复值的最大id及其重复的字段d1,d2共同作为b表.然后比较d1,d2值相当的值,但id小于最大id值,也可以是不等于,(大致意思就,取d1,d2值同时相等的,然后去id号不同的值),然后将其删除. 注意..在delete后面..只能取别名..不能写表名..不然会报错....
2.
delete from tet5
where id not in (select id
from (select max(id) id
from tet5
group by name
having count(*) > 1) a)
and id not in (select id
from (select max(id) id
from tet5
group by name
having count(*) = 1) b);
ps: 这个sql就相对好理解一些.第一个条件就是将重复值的最大id去除,第二个条件就是将不重复的值去除...剩下的就是需要删除的重复值.
阅读(1555) | 评论(0) | 转发(0) |