这是如何删除重复行的方法...我会给你写一个例子,你需要将其应用到你的代码中。我有一个演员表,其中包含ID
,我想删除重复的first_name
行。
mysql> select actor_id, first_name from actor_2;
+----------+-------------+
| actor_id | first_name |
+----------+-------------+
| 1 | PENELOPE |
| 2 | NICK |
| 3 | ED |
....
| 199 | JULIA |
| 200 | THORA |
+----------+-------------+
200 rows in set (0.00 sec)
-现在我使用一个名为@a的变量来获取下一行的ID,如果它们具有相同的first_name(重复),则获取其ID,否则返回null。
mysql> select if(first_name=@a,actor_id,null) as first_names,@a:=first_name from actor_2 order by first_name;
+
| first_names | @a:=first_name |
+
| NULL | ADAM |
| 71 | ADAM |
| NULL | AL |
| NULL | ALAN |
| NULL | ALBERT |
| 125 | ALBERT |
| NULL | ALEC |
| NULL | ANGELA |
| 144 | ANGELA |
...
| NULL | WILL |
| NULL | WILLIAM |
| NULL | WOODY |
| 28 | WOODY |
| NULL | ZERO |
+
200 rows in set (0.00 sec)
-现在我们只能获取重复的ID:
mysql> select first_names from (select if(first_name=@a,actor_id,null) as first_names,@a:=first_name from actor_2 order by first_name) as t1;
+
| first_names |
+
| NULL |
| 71 |
| NULL |
...
| 28 |
| NULL |
+
200 rows in set (0.00 sec)
最后一步,让我们删除!
mysql> delete from actor_2 where actor_id in (select first_names from (select if(first_name=@a,actor_id,null) as first_names,@a:=first_name from actor_2 order by first_name) as t1);
Query OK, 72 rows affected (0.01 sec)
-现在让我们来检查一下我们的表格:
mysql> select count(*) from actor_2 group by first_name;
+
| count(*) |
+
| 1 |
| 1 |
| 1 |
...
| 1 |
+
128 rows in set (0.00 sec)
它可以运行,如果您有任何问题,请回信给我。