MySQL 删除表中重复的记录（where......）

 

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)