MySQL按日期排序，按名称分组

Question

表

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 |
+--------+---------------------+

在上面的示例中，我希望先按created_at排序，并显示同名行。

所以我试着用created_at点菜，名字。

mysql> select * from temp order by created_at, name;
+--------+---------------------+
| 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 |
+--------+---------------------+

但它没有分组名称。

在搜索了order之后，我发现在第二列的情况下，它只适用于第一个order的结果。

期望结果

+--------+---------------------+
| 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 |
+--------+---------------------+

我如何操作查询来实现它？

Answer 1

您必须先按每个指定组的最小created_at进行排序，然后再通过created_at进行排序。

SELECT name, created_at
FROM TEMP tem
ORDER BY
    (SELECT MIN(tem2.created_at)
     FROM TEMP tem2 
     WHERE tem.name = tem2.name), created_at

每个行的名称组的名称部分将得到最小created_at

SELECT MIN(tem2.created_at)
         FROM TEMP tem2 
         WHERE tem.name = tem2.name

现在，每个组按照组的最小created_at排序，然后按created_at排序每个组的名称。

Answer 2

您可以使用每个名称的最小created_at子查询连接此表，并按此顺序排序：

SELECT   t.name, created_at
FROM     mytable t
JOIN     (SELECT   name, MIN(created_at) AS min_created_at
          FROM     mytable
          GROUP BY name) m ON t.name = m.name
ORDER BY min_created_at, t.name, t.created_at

Answer 3

如果您运行的是MySQL 8.0，您只需使用min()窗口进行排序：

select *
from temp
order by 
    min(created_at) over(partition by name), 
    created_at

窗口min()为所有具有相同name的记录提供了最小的name。

在早期版本中，我将直接在order by子句中使用关联子查询：

select *
from temp t
order by
    (select min(t1.created_at) from temp t1 where t1.name = t.name),
    created_at