1. 很悲剧,项目运行运行就出现重复数据,用的是mybatis,所以很悲剧。
2. 更悲剧,写了一句delete from sub_system where id not in (select max(b.id) as destId from sub_system b GROUP BY b.`name`);
mysql:[Err] 1093 - You can't specify target table 'sub_system' for update in FROM clause
突然想起mysql不支持这种写法;
3. 修正sql:delete from sub_system where id not in (select destid from (select max(b.id) as destId from sub_system b GROUP BY b.`name`) as dest);
1)(select max(b.id) as destId from sub_system b GROUP BY b.`name`) as dest 将查询出的结果放在一个集合dest中,其中列名为destid;
2) 查询destid从dest中;
3) 继续not in;
4) 执行成功;
疑问:mysql是不将所有的筛选结果都放在一个集合中?