[리트코드] 196. Delete Duplicate Emails

📢 본 포스팅에 활용되는 기본 문제 및 자료 출처는

       리트코드 Problems / https://leetcode.com/problemset/all/  임을 밝힙니다.

---

■ 문제

https://leetcode.com/problems/delete-duplicate-emails/description/

Delete Duplicate Emails - LeetCode

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

 

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest  id. Note that you are supposed to write a DELETE statement and not a SELECT one.

After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table.

The final order of the Person table does not matter.

The query result format is in the following example.

 

Example 1:

Input: 
Person table:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Output: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

---

■ 풀이

  문제 요구사항  

가장 작은 ID를 갖는 고유한 전자 메일이 되도록 중복되는 이메일을 삭제.

SELECT문이 아닌 DELETE문을 사용할 것 > DELETE FROM ~

 

방법1) email별로 그룹핑해서 가장 작은 id를 갖는 데이터가 지워지지 않도록 출력

DELETE FROM person
 WHERE id NOT IN (
     SELECT T1.min_id
      FROM (SELECT email, MIN(id) min_id
             FROM person
             GROUP BY 1) T1)

 

방법2) 셀프 조인으로 중복되는 이메일의 경우, 아이디 숫자가 큰 데이터를 삭제

DELETE P1
 FROM person P1
 INNER JOIN person P2
 ON P1.email = P2.email
 WHERE P1.id > P2.id