SQLで重複抽出
色々方法があるんだな、というLeetcodeのメモです。
- 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
関連しているかもしれない記事