SQL用例：去重

SQL用例：去重

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values ('1', 'a@b.com')
insert into Person (Id, Email) values ('2', 'c@d.com')
insert into Person (Id, Email) values ('3', 'a@b.com')

方法一：使用 GROUP BY 和临时表

select Email, count(Email) as num
from Person
group by Email;

| Email   | num |
|---------|-----|
| a@b.com | 2   |
| c@d.com | 1   |

以此作为临时表，我们可以得到下面的解决方案。

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

方法二：使用 GROUP BY 和 HAVING 条件

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