SQL删除BestellNummer与Type重复记录的查询语句原理解析

问题背景 #

技术面试中要求编写单条SQL，不借助临时表，删除auftrag表内BestellNummer、Type两个字段取值重复的冗余记录，最终采用的实现语句如下：

DELETE FROM auftrag WHERE ID NOT IN
(
    SELECT MIN(ID) FROM auftrag GROUP BY BestellNummer, Type
)

测试使用的auftrag表样例数据如下：

ID  BestellNummer   Type    Number   
0   123             O       1000
1   123             O       1001
2   123             E       1002
3   512             O       1003
4   512             O       1004
5   732             E       1005

语句实测会删除ID为1、4的重复记录，以下是逐段执行逻辑说明：

执行逻辑拆解 #

整条语句的执行顺序是先跑内层子查询得到要保留的记录ID集合，再在外层删除所有不在保留集合里的记录，分两步看：

• 第一步：执行内层子查询 SELECT MIN(ID) FROM auftrag GROUP BY BestellNummer, Type
  这部分会先把表里所有记录按BestellNummer+Type的组合做分组，两个字段值完全一样的记录会被归到同一组，之后从每组里取出最小的ID值——这个最小ID就是每组要留下的那条记录的标识。
  套入样例数据计算的话，分组和对应保留ID为：
  • 组合（123, O）：组内有ID 0、1两条记录，保留最小ID 0
  • 组合（123, E）：组内只有ID 2一条记录，保留ID 2
  • 组合（512, O）：组内有ID 3、4两条记录，保留最小ID 3
  • 组合（732, E）：组内只有ID 5一条记录，保留ID 5
    最终子查询返回的待保留ID列表为 (0, 2, 3, 5)
• 第二步：执行外层删除逻辑 DELETE FROM auftrag WHERE ID NOT IN (待保留ID列表)
  这一步会扫描全表所有记录，只要当前记录的ID不在刚才算出的待保留列表里，就判定为冗余重复记录，直接删除。对应样例数据里，ID 1、ID 4不在保留列表中，所以会被删掉，和实测结果完全匹配。

注意：这个写法的生效前提是ID字段为非空的唯一标识字段，如果ID列存在NULL值，NOT IN的判断逻辑会出现匹配异常，生产环境使用前需要先确认表字段约束。

内容的提问来源于stack exchange，提问作者ILikeSahne