java面试工作必备技能题 - SQL数据剔重

短命女 2023-03-01 09:59 16阅读 0赞

## 一. 题引 ##

编写一个 SQL 查询，来删除 **Person** 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个（Id 是这个表的主键）。

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th>Email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>john@example.com</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>bob@example.com</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>john@example.com</td> 
  </tr> 
 </tbody> 
</table>

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

**提示：**

1.  执行 SQL 之后，输出是整个 Person 表。
2.  使用 delete 语句。

--------------------

## 二. 看似ok的错误解法 ##

### 情况一 ###

delete from Person where Id not in (
        select Id from (
            select Id, Email from Person group by Email
        )
    )

运行报错：Every derived table must have its own alias

**错误原因：**  
每个sql里面产生的新表都必须要使用别名，加上表别名即可解决这个错误

### 情况二 ###

delete from Person where Id not in (
        select n.Id from (
            select m.Id, m.Email from Person m group by Email
        ) n
    )

**错误情况：**

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th>Email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>john@example.com</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>john@example.com</td> 
  </tr> 
 </tbody> 
</table>

运行select \* from Person后显示结果如下：

<table> 
 <thead> 
  <tr> 
   <th>Id</th> 
   <th>Email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>john@example.com</td> 
  </tr> 
 </tbody> 
</table>

主键Id并没有保留最小的

--------------------

## 三. 正确解法 ##

综上所述，得出正确解法如下：

delete from Person where Id not in (
        select n.Id from (
            select min(m.Id) Id, m.Email from Person m group by Email
        ) n
    )