SQL查询max聚合值加当日过滤条件时返回关联id与实际行不匹配

问题根源 #

你的SQL出错的核心原因是违反了聚合查询的字段匹配规则：

• 当查询使用了max()这类聚合函数且没有显式声明GROUP BY时，所有未被聚合函数包裹的字段（示例中的id、DAY(FROM_UNIXTIME(timestamp))）返回的都是符合WHERE条件的结果集中的随机值，数据库不会自动关联最大值对应的行的其他字段，因此会出现id和最高温度不匹配的问题。
• 额外注意：DAY()函数仅取日期的日数，跨月时会出现逻辑错误，比如10月20日和9月20日的DAY()返回值相同，会误匹配不同月份的同日数据，建议改用DATE()取完整日期做判断。

正确实现方案 #

方案1：仅查询今日最高温度对应行（最简单写法） #

按温度倒序排序后取第一条即可：

SELECT id, temperature, FROM_UNIXTIME(timestamp) AS record_time
FROM `zimmer_raumdaten`
WHERE DATE(FROM_UNIXTIME(timestamp)) = CURDATE()
ORDER BY temperature DESC
LIMIT 1;

方案2：同时查询今日、昨日各自的最高温度及对应id（MySQL 8.0+支持） #

使用窗口函数按天分组排序取每日最高：

WITH daily_temp_rank AS (
    SELECT 
        id, 
        temperature, 
        DATE(FROM_UNIXTIME(timestamp)) AS record_date,
        -- 按日期分组后对温度倒序排名，第一名即为当日最高温
        ROW_NUMBER() OVER (PARTITION BY DATE(FROM_UNIXTIME(timestamp)) ORDER BY temperature DESC) AS rank_num
    FROM `zimmer_raumdaten`
    -- 筛选今日和昨日的数据
    WHERE DATE(FROM_UNIXTIME(timestamp)) IN (CURDATE(), DATE_SUB(CURDATE(), INTERVAL 1 DAY))
)
SELECT id, temperature, record_date
FROM daily_temp_rank
WHERE rank_num = 1;

方案3：兼容MySQL 5.x的当日最高温查询写法 #

用子查询先拿到当日最高温度，再匹配对应的完整行：

SELECT id, temperature, FROM_UNIXTIME(timestamp) AS record_time
FROM `zimmer_raumdaten`
WHERE 
    DATE(FROM_UNIXTIME(timestamp)) = CURDATE()
    AND temperature = (
        SELECT MAX(temperature) 
        FROM `zimmer_raumdaten` 
        WHERE DATE(FROM_UNIXTIME(timestamp)) = CURDATE()
    )
-- 当日有多条相同最高温记录时仅返回一条，需要全部可删除此行
LIMIT 1;

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