注,仅适用于MYSQL8以上,不然无法使用窗口函数
示例数据如下:
lot_id file_name file_time type 13629111 13629111_20231101_165326.csv 2023-11-01 16:53:18 1 13629111 13629111_20231110_095855.csv 2023-11-10 09:58:31 2 13629111 13629111_20231110_084255.csv 2023-11-10 08:41:56 2 13629111 13629111_20231110_093731.csv 2023-11-10 08:41:56 2 1234 1234_20231102_134030.csv 2023-11-02 13:40:21 1 1234 1234_20231102_171750.csv 2023-11-02 17:17:31 1 1234 1234_20231106_122026.csv 2023-11-06 12:18:36 2
若想取出数据,按lot_id和type分组,依据file_time最新的那条数据,获取到file_name. 即取出如下数据
lot_id file_name file_time type 1234 1234_20231102_171750.csv 2023-11-02 17:17:00 1 1234 1234_20231106_122026.csv 2023-11-06 12:18:00 2 13629111 13629111_20231101_165326.csv 2023-11-01 16:53:00 1 13629111 13629111_20231110_095855.csv 2023-11-10 09:58:00 2
可使用如下sql完成
select lot_id, file_name, file_time, ` type` ,
ROW_NUMBER( ) over ( PARTITION BY lot_id, ` type` order by file_time desc ) as ` rank` FROM test;
select * from
(
select lot_id, file_name, file_time, ` type` ,
ROW_NUMBER( ) over ( PARTITION BY lot_id, ` type` order by file_time desc ) as ` rank` FROM test
) a where a. ` rank` = 1
其中
PARTITION BY lot_id,type. 语法类似与group by,指定分组要求. order by file_time desc即指定组内按什么排序. ROW_NUMBER()为窗口函数,记为组内的行号 注意:窗口函数不会改变原有的数据行数,可以认为是查看分析数据的一种方式,即在原数据上加上一列
上半句片段执行结果如下,rank表示按照需求的排序情况
lot_id file_name file_time type rank 13629111 13629111_20231101_165326.csv 2023-11-01 16:53:18 1 1 13629111 13629111_20231110_095855.csv 2023-11-10 09:58:31 2 1 13629111 13629111_20231110_084255.csv 2023-11-10 08:41:56 2 2 13629111 13629111_20231110_093731.csv 2023-11-10 08:41:56 2 3 1234 1234_20231102_134030.csv 2023-11-02 13:40:21 1 2 1234 1234_20231102_171750.csv 2023-11-02 17:17:31 1 1 1234 1234_20231106_122026.csv 2023-11-06 12:18:36 2 1