mysql 去除重複資料 語句

標籤：style   blog   http   color   strong   io   資料   for   

糾結的過程：

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 role   group 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 role   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 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://www.educity.cn/wenda/594988.html