如何实现MySQL按created_at排序后同name分组展示的查询？

Hey, I get what you're trying to do here—you want to group all rows with the same name together, ordered by when the first occurrence of each name appeared in the created_at column, and then sort rows within each name group by created_at as well. Let's break down how to make this work.

First, Let's Recap Your Data #

Your original temp table looks like this:

mysql> select * from temp;
+--------+---------------------+
| name   | created_at          |
+--------+---------------------+
| second | 2020-01-01 12:00:00 |
| first  | 2020-01-01 12:10:00 |
| second | 2020-01-01 12:20:00 |
| third  | 2020-01-01 12:30:00 |
| third  | 2020-01-01 12:40:00 |
| first  | 2020-01-01 12:50:00 |
+--------+---------------------+

Why Your Initial Query Failed #

Your first attempt SELECT * FROM temp ORDER BY created_at, name didn't group same-name rows because it sorts all rows globally by created_at first, then only sorts rows with identical created_at values by name. That's why your results had scattered name entries instead of grouped ones.

Method 1: Using a Subquery (Works for All MySQL Versions) #

We can first calculate the earliest created_at for each name, then use that value to sort our groups before sorting within each group:

SELECT t.*
FROM temp t
INNER JOIN (
    -- Get the earliest created_at for each name
    SELECT name, MIN(created_at) AS first_appearance
    FROM temp
    GROUP BY name
) name_timings ON t.name = name_timings.name
-- First sort groups by their first occurrence, then by name (for tiebreaks), then by created_at within the group
ORDER BY name_timings.first_appearance, t.name, t.created_at;

Method 2: Using Window Functions (MySQL 8.0+) #

If you're using MySQL 8.0 or newer, window functions make this cleaner. We can compute the first occurrence of each name directly in the query without a join:

SELECT name, created_at
FROM (
    SELECT *,
           -- Calculate the earliest created_at for the name of each row
           MIN(created_at) OVER (PARTITION BY name) AS first_appearance
    FROM temp
) grouped_data
ORDER BY first_appearance, name, created_at;

Both Methods Will Return Your Desired Result #

+--------+---------------------+
| name   | created_at          |
+--------+---------------------+
| second | 2020-01-01 12:00:00 |
| second | 2020-01-01 12:20:00 |
| first  | 2020-01-01 12:10:00 |
| first  | 2020-01-01 12:50:00 |
| third  | 2020-01-01 12:30:00 |
| third  | 2020-01-01 12:40:00 |
+--------+---------------------+

内容的提问来源于stack exchange，提问作者Hide