Shulou(Shulou.com)06/01 Report--

Let me start with a brief description of the functional scenario:

The data row fields are as follows:

Name

Started_at

Type

In this table, name has duplicate values

Now you need to filter out a list when determined by type. If the name is satisfied, it will not be duplicated, and the record found will be the smallest record with the started_at under name.

For example:

Activity 1 2019-06-01 type1

Activity 1 2019-06-02 type1

Activity 1 2019-06-03 type1

Activity 2 2019-06-03 type1

Activity 2 2019-06-05 type1

Activity 2 2019-06-07 type1

The list of the screen should be:

Activity 1 2019-06-01 type1

Activity 2 2019-06-03 type1

Also need to satisfy that the started_at is greater than the current time.

May I ask how to write such a sql?

The solution is:

Is to use left join himself.

For example, S1 left join S2 on s1.name=s2.name 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

Do you have any better solutions?

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.