Some notes on mysql self-join deduplication

Let me briefly explain the functional scenario:

The data row fields are as follows:

name
started_at
type

In this table, name has duplicate values

Now we need to filter out a list when the type is determined, so that the name is not repeated and the record with the smallest started_at under the same name is found.

For example:

Event 1 2019-06-01 type1
Event 1 2019-06-02 type1
Event 1 2019-06-03 type1

Event 2 2019-06-03 type1
Event 2 2019-06-05 type1
Event 2 2019-06-07 type1

The sieve list should be:

Event 1 2019-06-01 type1
Event 2 2019-06-03 type1

It also needs to satisfy that started_at is greater than the current time

How should I write such sql?

The solution is:

Is to use left join itself

For example, s1 left join s2 on s1.name=s2.name and s2.started_at<s1.started_at and s2.started_at > now()

Finally where s2.id is null

SELECT
 s1.NAME,
 s1.started_at,
 
FROM
 tbl s1
 LEFT JOIN tbl s2 ON s1.`name` = s2.`name` 
  AND s1.started_at > s2.started_at 
  AND s2.started_at > now() 
WHERE
  s2.id IS NULL 
  AND s1.started_at > now() 
 AND s1.type = 'online_lecture'
ORDER BY
 s1.NAME,
 s1.started_at;

Does anyone have any better solution?

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.