删除以id为主键地重复记录
要求能够通过一个sql语句删除所有重复的记录,并只留下重复记录中的一条数据
思路:
1、查找所有的重复数据,并获取最小(大)ID的一条记录 table = t3
2、查找是重复数据,并且比 1中ID 大(小)的数据数据ID table = t1 join t3
3、删除2中找到的ID数据 table = t4
1 --- 删除重复的角色权限配置 2 DELETE FROM sys_roleright_t t4 WHERE t4.FRIGHTID IN ( 3 SELECT t1.FRIGHTID 4 FROM sys_roleright_t t1 5 JOIN (SELECT min(t2.FRIGHTID) AS FRIGHTID, t2.FROLEID, t2.FAPPID, count(0) 6 FROM sys_roleright_t t2 7 GROUP BY t2.FROLEID, t2.FAPPID 8 HAVING count(0) > 1 9 ) t3 ON t1.FROLEID = t3.FROLEID AND t1.FAPPID = t3.FAPPID 10 WHERE t1.FRIGHTID > t3.FRIGHTID 11 );