Mysql按日期时间的日期部分进行分组,并为每个日期选择具有最大日期时间的行

使用 ROW_NUMBER 我们可以尝试:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(log_time)
                                 ORDER BY log_time DESC) rn
    FROM usr_weight
    WHERE user_id = 1
)

SELECT user_id, weight, log_time
FROM cte
WHERE rn = 1;

这里有一种老派的加入方式:

SELECT uw1.user_id, uw1.weight, uw1.log_time
FROM usr_weight uw1
INNER JOIN
(
    SELECT DATE(log_time) AS log_time_date, MAX(log_time) AS max_log_time
    FROM usr_weight
    WHERE user_id = 1
    GROUP BY DATE(log_time)
) uw2
     ON uw2.log_time_date = DATE(uw1.log_time) AND
        uw2.max_log_time = uw1.log_time
WHERE
    uw1.user_id = 1;