A表
+----+--------+----------+
| id | typeid | name |
+----+--------+----------+
| 1 | 1 | AA |
| 2 | 1 | BB |
| 3 | 1 | AA |
| 4 | 2 | A |
| 5 | 2 | A |
| 6 | 1 | BB |
+----+--------+----------+id这个没有要求,只要去掉一个类别下重复的name即可,结果希望如下:
+----+--------+----------+
| id | typeid | name |
+----+--------+----------+
| 1 | 1 | AA |
| 2 | 1 | BB |
| 4 | 2 | A |
+----+--------+----------+
+----+--------+----------+
| id | typeid | name |
+----+--------+----------+
| 1 | 1 | AA |
| 2 | 1 | BB |
| 3 | 1 | AA |
| 4 | 2 | A |
| 5 | 2 | A |
| 6 | 1 | BB |
+----+--------+----------+id这个没有要求,只要去掉一个类别下重复的name即可,结果希望如下:
+----+--------+----------+
| id | typeid | name |
+----+--------+----------+
| 1 | 1 | AA |
| 2 | 1 | BB |
| 4 | 2 | A |
+----+--------+----------+
给出了好几种解决方法,自己看下吧
mysql> select * from t a where not exists(select 1 from t b where b.typeid=a.typ
eid and b.name=a.name and id<a.id) //
+------+--------+------+
| id | typeid | name |
+------+--------+------+
| 1 | 1 | aa |
| 2 | 1 | bb |
| 4 | 2 | a |
+------+--------+------+
3 rows in set (0.00 sec)
删除同类别下面name重复的记录,重复的需要保留一条
mysql> delete b from t a ,t b where a.typeid=b.typeid and a.name=b.name and a.id
<b.id //
Query OK, 3 rows affected (0.02 sec)mysql> select * from t //
+------+--------+------+
| id | typeid | name |
+------+--------+------+
| 1 | 1 | aa |
| 2 | 1 | bb |
| 4 | 2 | a |
+------+--------+------+
3 rows in set (0.00 sec)不好意思,还不是很熟悉。