查询每天，每月数据，只取整点的数据

最新推荐文章于 2024-07-30 02:08:36 发布

月渐盈

最新推荐文章于 2024-07-30 02:08:36 发布

阅读量1.5k

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_34471241/article/details/114025675

版权

使用 mysql DATE_FORMAT 函数

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

查询所有数据，取整点数据

SELECT
	DATE_FORMAT(
		send_time,
		'%Y-%m-%d %k:00:00'
	) send_time,id
	
FROM
	water_data_item
WHERE
	1 = 1

GROUP BY
	DATE_FORMAT(
		send_TIME,
		'%Y-%m-%d %k:00:00'
	) 
ORDER BY
	send_TIME

查询结果

查询当天的数据，每小时取整点数据

SELECT 
	id ,
    DATE_FORMAT(
		send_time,
		'%Y-%m-%d %k:00:00'
	  ) ,
	`sequence`,
	region_code ,
	CODE,
	address,
	event_type ,
	water_tmp_val ,
	ph_val ,
	diandl_val ,
	rongy_val ,
	zhuodu_val
from water_data_item WHERE year(send_time)=year(NOW()) AND MONTH(send_time)=MONTH(NOW()) and  DAY(send_time)=DAY(NOW())
 GROUP BY
	DATE_FORMAT(
		send_TIME,
		'%Y-%m-%d %k:00:00'
	)
ORDER BY
	send_time DESC Limit 100

查询结果

sql拼接，多个结果放到一起

单个sql

select count(*) from fire_alarm_event where to_days(alarm_time) = to_days(now()) and is_deleted = 0;   //查询当天数据--今日报警次数  alarmToday

select count(*) from fire_alarm_event where to_days(alarm_time) = to_days(now()) and is_deleted = 0 AND event_type = 1; //当天成灾火灾次数 alarmTodayFire;

SELECT count(*) FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and is_deleted = 0    //本月告警总次数  alarmMonth

SELECT * FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) 
and is_deleted = 0 AND   alarm_state <> '未核实'       //本月报警处理数次数  alarmDeal;

SELECT count(*) FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) 
and is_deleted = 0  AND  event_type = 1  //本月设备误报次数  alarmErrorDay;


SELECT count(*) FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) 
and is_deleted = 0  AND  event_type = 3 //本月设备故障次数  alarmFaultMon;

拼接的结果

SELECT a.b alarmToday,c.d alarmTodayFire ,e.f alarmMonth,g.h alarmDeal,i.j alarmErrorDay ,k.l alarmFaultMon,g.h/e.f alarmDealRate FROM
(select count(*) b  from fire_alarm_event where to_days(alarm_time) = to_days(now()) and is_deleted = 0)a,
(select count(*) d   from fire_alarm_event where to_days(alarm_time) = to_days(now()) and is_deleted = 0 AND event_type = 1)c,
(SELECT count(*) f FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and is_deleted = 0 )e,
(SELECT count(*) h FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and is_deleted = 0 AND   alarm_state <> '未核实' )g,
(SELECT count(*) j FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and is_deleted = 0  AND  event_type = 1)i,
(SELECT count(*) l FROM fire_alarm_event WHERE DATE_FORMAT( alarm_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) 
and is_deleted = 0  AND  event_type = 3 )k