mysql查询数据时,按照时间分组求和

最新推荐文章于 2024-10-15 18:00:00 发布

原创最新推荐文章于 2024-10-15 18:00:00 发布 · 1w 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据

数据库专栏收录该内容

8 篇文章

订阅专栏

本文通过两个SQL查询案例，介绍了如何使用分组函数group by进行数据分组，并结合求和函数sum来汇总特定字段的数据。此外，还展示了如何通过条件判断函数处理聚合结果。

1、查询时，同一时间点有多条数据

SELECT
	date_format(ctime, '%Y-%m-%d %H:%i:%s') ctime,
	mode_num as mode_num,
	nose_num_real as nose_num_real,
	t2.num as num ,
	capacity,
	limitPower
FROM
	rpps_prod_minute_theory t1
LEFT JOIN rpps_data_power t2 ON t1.ctime = t2.ptime
AND t1.group_id = t2.group_id
WHERE
	ctime >='2017-10-10'
AND ctime <'2017-10-11'
AND t1.farm_id ='1ff499730a72412ebd174b77b31327e1'
ORDER BY
	ttime

2、此处用到了group by 分组函数，根据时间分组。和求和函数sum ，再对每一项求和

SELECT
	date_format(ctime, '%Y-%m-%d %H:%i:%s') ctime,
	sum(mode_num) AS mode_num,
	sum(nose_num_real) AS nose_num_real,
	sum(t2.num) AS num,
	capacity,
	(sum(limitPower) > 0) AS limitPower
FROM
	rpps_prod_minute_theory t1
LEFT JOIN rpps_data_power t2 ON t1.ctime = t2.ptime
AND t1.group_id = t2.group_id
WHERE
	ctime >='2017-10-10'
AND ctime <'2017-10-11'
AND t1.farm_id ='1ff499730a72412ebd174b77b31327e1'
GROUP BY	ttime
ORDER BY
	ttime