MYSQL 数据查询

参考资料：
https://blog.csdn.net/qq_43511677/article/details/118899906?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-4-118899906-blog-90376824.t0_edu_mix&spm=1001.2101.3001.4242.3&utm_relevant_index=6
https://download.csdn.net/download/hansen99k/10269205?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-download-2%7Edefault%7ECTRLIST%7EPaid-2-10269205-blog-90376824.t0_edu_mix&depth_1-utm_source=distribute.pc_relevant.none-task-download-2%7Edefault%7ECTRLIST%7EPaid-2-10269205-blog-90376824.t0_edu_mix&utm_relevant_index=4

查询当月数据：
SELECT * FROM data_ts WHERE DATE_FORMAT(created_at,'%Y%m') = DATE_FORMAT(curdate(),'%Y%m');

查询上月数据：
SELECT * FROM data_ts WHERE PERIOD_DIFF(DATE_FORMAT(now(),'%Y%m'),DATE_FORMAT(created_at,'%Y%m'))=1

查询上月数据的最后一条：
SELECT * FROM data_ts WHERE PERIOD_DIFF(DATE_FORMAT(now(),'%Y%m'),DATE_FORMAT(created_at,'%Y%m'))=1 ORDER BY id DESC LIMIT 1

查询当前日期的上一天数据：
day = -1 //day等于-1代表当前日期的前一天

　let info  = await this.app.model.query(

                    'SELECT day_power,run_time FROM data_a WHERE devId = $sta AND DATEDIFF(created_at,NOW())=$day  ORDER BY id DESC LIMIT 1;', {bind:{sta:com[i],day:day}},  

　　　　　　　　　　　　'SELECT day_power,run_time FROM data_a WHERE devId = $sta AND DATEDIFF(created_at,NOW())=-1  ORDER BY id DESC LIMIT 1;', {bind:{sta:com[i],day:day}}, 

                );

 

 删除指定日期的数据：

 DELETE FROM argsMessage WHERE DATE_FORMAT(createdat,'%Y%m') ='202211'

 

 查询指定日期：

 SELECT * FROM argsMessage WHERE DATE_FORMAT(createdat,'%Y%m') ='202211'

 

 

-- 分组实现group by后取各分组的最新一条
SELECT codedaddress,device_name,date,alarm_message FROM gw_seven_alarm_data td JOIN( SELECT max(id) id  FROM gw_seven_alarm_data GROUP BY device_name ) md WHERE td.id = md.id AND date BETWEEN DATE_SUB(NOW(),INTERVAL 5 MINUTE) AND NOW()