1.dept 表:
ID | NAME |
---|---|
100 | AAA |
100 | BBB |
100 | CCC |
101 | DDD |
101 | EEE |
102 | FFF |
103 | GGG |
2.目标描述:
dept表中, ID字段100出现3次, 101出现2次, 其余出现1次, 通过一句MySQL语句将ID字段重复值对应的记录删除, 只保留其中一条, 即: 100对应的记录需要删除2行, 101记录删除1行
3.思路描述
1)定位出ID字段出现重复的值. 本例中的策略是分组group by后使用count函数:
select ID from dept group by ID having count(ID)>1;
2)明确保留策略. 仅仅能定位出重复值不足以达到保留其中一条的目的, 也就是说, 必须明确保留重复值记录中哪一条, 本例中的策略是使用min 或者 max确定唯一一条记录:
select min(NAME) as NAME from dept group by ID having count(ID)>1;
3)结合前两点定位出要删除的目标, 即
select * from dept
where ID in 策略1)定位的记录
and NAME not in 策略2)定位的记录;
具体的SQL语句:
select * from dept
where ID in (select ID from dept group by ID having count(ID)>1)
and NAME not in (select min(NAME) as NAME from dept group by ID having count(ID)>1);
4.根据思路描述, 写出删除语句.
delete from dept
where ID in (select ID from(select ID from dept group by ID having count(ID)>1) as duplicate)
and NAME not in (select NAME from (select min(NAME) as NAME from dept group by ID having count(ID)>1) as retain);
与3中的3)比较会发现删除语句比选择语句多嵌套了一层, 为什么?自力更生, 丰衣足食哈哈哈, 这里不是说想卖关子, 而是希望这篇文章落笔于关注点, 相关问题会提到但在本文不予与解决.