MySQL 删除表中重复的记录，只保留一条

1. 使用临时表

2.

delete from at1 where id in (select * from (select max(id) from at1 group by str having count(str) > 1) as b);

or

delete a from at1 as a, at1 as b where a.str=b.str and a.id > b.id;

3. 多个字段重复的问题。

“name”值存在重复的项； Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

（见http://hi.baidu.com/yangjifu/blog/item/723e5f4cf92708ffd72afc92.html）

-----------------------------------------

1.MySQL的delete 支持：

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id  

2. 从 MySQL 4.0.4 开始，你也可以执行一个包含多个表的 UPDATE 的操作：

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

注意：多表 UPDATE 不可以使用 ORDER BY 或 LIMIT。

3. 查询一个表中重复的记录：

select max(id) from at1 group by str having count(str) > 1

select a.id from at1 as a, at1 as b where a.str=b.str and a.id > b.id

4. delete from at1 where id in (select max(id) from at1 group by str having count(str) > 1); 是错误的：

ERROR 1093 : You can't specify target table 'at1' for update in FROM clause

正确写法：delete from at1 where id in (select * from (select max(id) from at1 group by str having count(str) > 1) as b);

理由（文档）：

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

mysql还支持 where (a,b) in (select a, b from aaaa)