Public account: You and the cabin by: Peter Editor: Peter

LeetCode-182- Find duplicate email addresses

Hello, I’m Peter

This article covers topic 182 of Leetcode-SQL, which is called finding duplicate E-mail addresses.

Difficulty level: easy, done discovery is really easy!

The title

Here’s the specific problem: Find duplicate E-mail addresses from the given table Person

Train of thought

Personal Approach 1

Group statistics according to each Email address. If the statistics number is greater than 1, it indicates that the Email address is duplicate. Run the same code twice, the difference is huge!

select 
    Email
from Person 
group by Email
having count(Email) > 1;  -- Filtration conditions
Copy the code

Personal Method 2

The idea of method 2 is similar to method 1, except that it uses an intermediate result table and is filtered by a WHERE condition. Method 1 uses the having condition to filter:

select
    t.Email
from(select   -- Count the number of emails
        Email
        ,count(*) as number  - the number of
    from Person 
    group by Email
)t  -- Provisional results table
where t.number > 1;  -- Select emails whose number is greater than 1
Copy the code

The effect of temporary results table T:

The reference method

Select * from table_name; select * from table_name; select * from table_name;

select 
    distinct (p1.Email)  -- Recalculate email
from Person p1
join Person p2 on p1.Email = p2.Email and p1.Id ! = p2.Id;  -- Specify connection conditions
Copy the code