mysql 用 group by 查询某段时间内每天的相关数据

主要用了 group by 对 time 字段分组查询

1、查询某个月内每天的最新数据

SELECT * FROM pm p 
WHERE p.id= 961 
AND p.time IN(
      SELECT MAX(time) FROM pm 
      WHERE id= 961 
      AND time BETWEEN '2021-03-01 00:00:00' AND '2021-03-30 23:59:59' 
      GROUP BY DATE_FORMAT(time,'%Y-%m-%d')
) 
GROUP BY time   #去除重复时间的记录
ORDER BY time DESC;

子查询里用分组查询每天最大的时间点，然后主句通过时间去查询记录

2、要查询一段时间内每天的平均值

SELECT DATE_FORMAT(time,'%Y-%m-%d') time_, AVG(value) FROM table_name
WHERE id= 34 
AND time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 23:59:59' 
GROUP BY time_;

注：GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。