my opinion is my own

SQLで重複抽出

色々方法があるんだな、というLeetcodeのメモです。

  1. Duplicate Emails

テーブル

mysql> select * from Person;
+------+---------+
| id   | email   |
+------+---------+
|    1 | a@b.com |
|    2 | c@d.com |
|    3 | a@b.com |
+------+---------+

HAVING句を使う

select Email
from Person
group by Email
having count(Email) > 1;

サブクエリ

select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;

自己結合

select distinct a.email from Person as a
join Person as b
on a.email = b.email
where a.Id <> b.Id
---

関連しているかもしれない記事


#SQL