mysql 去除重复数据 语句

纠结的过程：

mysql> select * from role group by role_name,deleted;+---------+-----------+---------+| role_id | role_name | deleted |+---------+-----------+---------+| 2| xue| 12  || 1| zhao  | 12  || 3| zhao  | 13  |+---------+-----------+---------+3 rows in setmysql>  delete from role c where c.role_id not in (select b.role_id from role c group by role_name,deleted);1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where c.role_id not in (select b.role_id from role c group by role_name,deleted)' at line 1mysql>  select *  from role c where c.role_id not in (select b.role_id from role b  group by role_name,deleted);+---------+-----------+---------+| role_id | role_name | deleted |+---------+-----------+---------+| 4| xue| 12  |+---------+-----------+---------+1 row in setmysql>  delete from role c where c.role_id not in (select b.role_id from role b  group by role_name,deleted);1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where c.role_id not in (select b.role_id from role b  group by role_name,deleted' at line 1mysql>  delete from role  where role_id not in (select b.role_id from role b  group by role_name,deleted);1093 - You can't specify target table 'role' for update in FROM clausemysql>  delete from role  where role_id not in (select role_id from rolegroup by role_name,deleted);1093 - You can't specify target table 'role' for update in FROM clausemysql>  delete from role  where not role_id  in (select role_id from rolegroup by role_name,deleted);1093 - You can't specify target table 'role' for update in FROM clausemysql>  delete from role  where role_id not in (select t.role_id from role t  group by role_name,deleted);1093 - You can't specify target table 'role' for update in FROM clausemysql>  delete from role  where role_id not in (select t.role_id from role t  group by t.role_name,t.deleted);1093 - You can't specify target table 'role' for update in FROM clausemysql>  delete from role  where role_id NOT IN (select t.role_id from role t  group by role_name,deleted);1093 - You can't specify target table 'role' for update in FROM clausemysql>  delete from role  where role_id NOT IN select *  from ( (select t.role_id from role t  group by role_name,deleted));1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select *  from ( (select t.role_id from role t  group by role_name,deleted))' at line 1mysql>  delete from role  where role_id NOT IN ( select *  from ( (select t.role_id from role t  group by role_name,deleted)));1248 - Every derived table must have its own aliasmysql>  delete from role  where role_id NOT IN ( select *  from  ( select t.role_id from role t  group by t.role_name,t.deleted));1248 - Every derived table must have its own alias

终于通了：

mysql>  delete from role  where role_id NOT IN ( select *  from  ( select t.role_id from role t  group by t.role_name,t.deleted) t1);Query OK, 1 row affected

参考：
mysql比较作呕的一个delete in操作
http://wenda/594988.html