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

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

3、(sum(limitPower) > 0) AS limitPower 此处对查询出来的数组做个大小判断，若比较成立则返回1，若不成立则返回0