颜林林的个人网站

Linlin Yan's Personal Website

MySQL中分组后选择首行

2021-01-13 08:31
#mysql #partition #group-by

在数据库表操作中,一个常见的需求是:如何按照创建时间,取出每天的第一条的记录。

然而,这并不是单纯依靠标准SQL语法就能完成的,而需要配合特定数据库实现的函数辅助才行。在MySQL中,这个函数即ROW_NUMBER()

下面用一个临时创建的表,具体展示下用法:

$ mysql

mysql> create table test0113 (grp int, value float);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test0113 values (1, .1), (1, .2), (1, .3), (2, .2), (2, .4);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test0113;
+------+-------+
| grp  | value |
+------+-------+
|    1 |   0.1 |
|    1 |   0.2 |
|    1 |   0.3 |
|    2 |   0.2 |
|    2 |   0.4 |
+------+-------+
5 rows in set (0.00 sec)

ROW_NUMBER()需要配合OVER (PARTITION BY ... ORDER BY ...)的语法结构,来实现所需查询:

mysql> select grp, value, row_number() over (partition by grp order by value) row_num from test0113 order by grp, value;
+------+-------+---------+
| grp  | value | row_num |
+------+-------+---------+
|    1 |   0.1 |       1 |
|    1 |   0.2 |       2 |
|    1 |   0.3 |       3 |
|    2 |   0.2 |       1 |
|    2 |   0.4 |       2 |
+------+-------+---------+
5 rows in set (0.00 sec)

有了这个结果后,再配合一层嵌套查询,就可以实现最开始的需求了:

mysql> select * from (select grp, value, row_number() over (partition by grp order by value) row_num from test0113 order by grp, value) t1 where row_num = 1;
+------+-------+---------+
| grp  | value | row_num |
+------+-------+---------+
|    1 |   0.1 |       1 |
|    2 |   0.2 |       1 |
+------+-------+---------+
2 rows in set (0.00 sec)

参考: