[LeetCode] 196. Delete Duplicate Emails (MySQL)

문제

코드

중복이 되는 이메일 삭제하되 ID가 작은 이메일 한개만 남기는 문제 

• DELETE JOIN

DELETE A
FROM Person A INNER JOIN Person B ON A.Email = B.Email
WHERE A.id > B.id

• Subquery

-- subquery type
DELETE FROM Person
WHERE Id NOT In (Write a subquery which only contains Ids should be deleted);

# 1. keeping 해야하는 이메일 구하기

SELECT email, MIN(Id)
FROM person
GROUP BY email

# 2. min_id만 가져오기

SELECT sub.min_id
FROM (
    SELECT email, MIN(Id) min_id
    FROM person
    GROUP BY email
) sub

# 3. 제일 작은 아이디 제외하고 DELETE 

DELETE FROM Person
WHERE id NOT IN (
    SELECT sub.min_id
    FROM (
        SELECT email, MIN(Id) min_id
        FROM person
        GROUP BY email
    ) sub
)

출처

https://www.mysqltutorial.org/mysql-delete-join/