mysql 删除相同数据

最近在研究sql优化,设计到删除重复信息时，遇到些小问题，网上查了些资料，在此记录一下；

             user表

id  username  password

1   admin        123456

2   admin         654321

3   tom            123456

-- 根据某一字段(username)，查询全部重读记录

select * from user where username in(select username from user group by username having Count(*)>=2)

结果:

id   username  password

1    admin        123456

2    admin         654321

-- 筛选重复记录(只显示一条，相同记录保留id的最小值)

select * from user where id not in(select max(id)  from user group by username having count(*)>=2)

结果:

id   username  password

1    admin        123456

3    tom            123456

-- 删除全部重复记录(只保留一条，id最小的)

delete from user where  id in (select id from (select max(id) id from user group by username having count(*)>=2) as u)

-- 查询记录

select * from user

结果:

id   username  password

1    admin        123456

3    tom            123456

由此可见 id=2被删除了

其中 有人疑惑，为什么sql语句不这样写

delete from user where id in(select max(id) from user group by username having count(*)>=2)

但这样写会报错  You can't specify target table <tbl> for update in FROM clause

刚开始自己也很疑惑，后来借鉴了一下别人的资料才知道。

MySQL中 不能先select出同一表中的某些值，再update这个表(在同一语句中)，也就是说你不能 对同一张表先selete 然后在 delete update  ，所以只能通过一张中间表来操作了， 其实这里的中间表也是 自表查询，只是取了个别名，数据库就不认为是同一张表了。