MySQL查询：按device分组获取各分组最新时间戳的多列数据

需要查询数据表中的多列数据，其中device列仅包含"top"和"bot"两个唯一值，每行数据带有datetime时间戳，目标是获取每个device对应的最新datetime时间戳的完整记录。

数据表结构与数据 #

uid | datetime           | device | temp | hum
================================================
1   |2022-08-30 17:34:34 |top     |11.5  |88.90
2   |2022-08-30 17:34:22 |bot     |13.2  |88.90
3   |2020-10-06 13:48:33 |top     |24.3  |75.00
4   |2020-10-06 14:35:37 |bot     |21.7  |75.00

期望查询结果 #

datetime            | device | temp | hum
===========================================
2022-08-30 17:34:34 |top     |11.5  |88.90
2022-08-30 17:34:22 |bot     |13.2  |88.90

当前问题 #

执行现有SQL语句时，返回的是每个device的最早记录而非最新：

返回的错误结果 #

datetime            | device | temp | hum
===========================================
2020-10-06 13:48:33 |top     |24.3  |75.00
2020-10-06 14:35:37 |bot     |21.7  |75.00

当前使用的SQL语句 #

SELECT datetime, device, temp, hum 
FROM <table_name> 
WHERE uid=123456789 
GROUP BY device 
ORDER BY datetime DESC 
LIMIT 2

错误原因：GROUP BY device会默认取每组的第一条记录（不同数据库行为有差异，但均不会自动选取最新数据），后续的ORDER BY仅对分组后的结果排序，无法改变分组时选取的原始记录。

方法1：使用窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

利用ROW_NUMBER()窗口函数按device分组，每组内按datetime降序排序，取排序后行号为1的记录：

SELECT datetime, device, temp, hum
FROM (
    SELECT 
        datetime, device, temp, hum,
        ROW_NUMBER() OVER (PARTITION BY device ORDER BY datetime DESC) AS rn
    FROM <table_name>
    WHERE uid=123456789
) t
WHERE rn = 1;

方法2：子查询关联（兼容旧版MySQL等不支持窗口函数的数据库） #

先通过子查询找出每个device对应的最大datetime，再关联原表获取完整记录：

SELECT t1.datetime, t1.device, t1.temp, t1.hum
FROM <table_name> t1
JOIN (
    SELECT device, MAX(datetime) AS max_dt
    FROM <table_name>
    WHERE uid=123456789
    GROUP BY device
) t2 ON t1.device = t2.device AND t1.datetime = t2.max_dt
WHERE t1.uid=123456789;

方法3：MAX()结合GROUP_CONCAT（仅MySQL适用，需注意字段类型兼容性） #

通过GROUP_CONCAT按datetime降序拼接字段，再提取第一个值：

SELECT 
    SUBSTRING_INDEX(GROUP_CONCAT(datetime ORDER BY datetime DESC), ',', 1) AS datetime,
    device,
    SUBSTRING_INDEX(GROUP_CONCAT(temp ORDER BY datetime DESC), ',', 1) AS temp,
    SUBSTRING_INDEX(GROUP_CONCAT(hum ORDER BY datetime DESC), ',', 1) AS hum
FROM <table_name>
WHERE uid=123456789
GROUP BY device;

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