query result(14 records)
id | uid | gid |
1 | 11 | 502 |
2 | 107 | 502 |
3 | 100 | 503 |
4 | 110 | 501 |
5 | 112 | 501 |
6 | 104 | 502 |
7 | 100 | 502 |
8 | 100 | 501 |
9 | 102 | 501 |
10 | 104 | 502 |
11 | 100 | 502 |
12 | 100 | 501 |
13 | 102 | 501 |
14 | 110 | 501 |
方法一
mysql> create temporary table tmp_wrap select * from users_groups group by uid having count(1) >= 1;
Query OK, 7 rows affected (0.11 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> truncate table users_groups;
Query OK, 14 rows affected (0.03 sec)
mysql> insert into users_groups select * from tmp_wrap;
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from users_groups;
query result(7 records)
id | uid | gid |
1 | 11 | 502 |
2 | 107 | 502 |
3 | 100 | 503 |
4 | 110 | 501 |
5 | 112 | 501 |
6 | 104 | 502 |
9 | 102 | 501 |
mysql> drop table tmp_wrap;
Query OK, 0 rows affected (0.05 sec)
2、还有一个很精简的办法。
查找重复的,并且除掉最小的那个。
delete users_groups as a from users_groups as a,
(
select *,min(id) from users_groups group by uid having count(1) > 1
) as b
where a.uid = b.uid and a.id > b.id;
(
select *,min(id) from users_groups group by uid having count(1) > 1
) as b
where a.uid = b.uid and a.id > b.id;
(7 row(s)affected)
(0 ms taken)
(0 ms taken)
query result(7 records)
id | uid | gid |
1 | 11 | 502 |
2 | 107 | 502 |
3 | 100 | 503 |
4 | 110 | 501 |
5 | 112 | 501 |
6 | 104 | 502 |
9 | 102 | 501 |