mysql - 减去 MySQL 中 2 个特定行的值
问题描述
我正在通过 MySQL 进行计算。下一个值是天值,现在我想看看最后一行和前 1 天(= 96 个季度)之间的区别。
下面的行是最后一个和最新的值:
SET @now = (SELECT * FROM solar.measurements WHERE `tag` LIKE '%18223.Inepro_Total_Forward_kWh[3]%' ORDER BY `measurements`.`timestamp` DESC LIMIT 1);
下一行是 1 天前的值。这与 96 个季度相同。
SET @yesterday = (SELECT * FROM solar.measurements WHERE `tag` LIKE '%18223.Inepro_Total_Forward_kWh[3]%' ORDER BY `measurements`.`timestamp` DESC LIMIT 95,1);
现在我想用 1 天前的值减去最新值,所以我知道这些值之间的差异。下面的代码是我现在创建的:
CREATE EVENT Value_Test
ON SCHEDULE EVERY 15 MINUTE
STARTS '2017-08-20 16:36:00'
DO
SET @now = (SELECT * FROM solar.measurements WHERE `tag` LIKE '%18223.Inepro_Total_Forward_kWh[3]%' ORDER BY `measurements`.`timestamp` DESC LIMIT 1);
SET @yesterday = (SELECT * FROM solar.measurements WHERE `tag` LIKE '%18223.Inepro_Total_Forward_kWh[3]%' ORDER BY `measurements`.`timestamp` DESC LIMIT 95,1);
SET @value = (@now - @yesterday);
INSERT INTO solar.measurements (nad, tag, value, timestamp) VALUES (18223, 'c18223.ValueDayTest', @waarde, CURRENT_TIMESTAMP());
当我想执行此操作时,出现以下错误:错误代码:1048。列“值”不能为空。有人可以通过找到解决方案来帮助我吗?谢谢!
解决方案
ASHOW CREATE EVENT Value_Test
会告诉您您的活动没有正确完成。
尝试这个:
Delimiter //
CREATE EVENT Value_Test
ON SCHEDULE EVERY 15 MINUTE
STARTS '2018-08-20 16:36:00'
DO
BEGIN
SET @now = (SELECT value FROM solar.measurements WHERE `tag` LIKE
'%18223.Inepro_Total_Forward_kWh[3]%' ORDER BY `measurements`.`timestamp` DESC
LIMIT 1);
SET @yesterday = (SELECT value FROM solar.measurements WHERE `tag` LIKE
'%18223.Inepro_Total_Forward_kWh[3]%' ORDER BY `measurements`.`timestamp` DESC
LIMIT 95,1);
SET @value = (@now - @yesterday);
INSERT INTO solar.measurements (nad, tag, value, timestamp) VALUES (18223,
'c18223.ValueDayTest', @waarde, CURRENT_TIMESTAMP());
END
//
Delimiter ;
如果需要在 EVENT 中使用复合语句,您需要将它们放在 Begin/End 下
推荐阅读
- reactjs - 将 App 包含在 Context 中后,屏幕未呈现任何内容
- amazon-web-services - 通过 .ebextentions 设置环境变量时出现 AWS Elastic Beanstalk 错误
- gis - 如何创建负缓冲距离
- firebase - 如何将从 Firebase 检索到的值存储到变量 Flutter 中
- c# - Windows 窗体:从网站到 dataGridView 的 XML
- arrays - 从数组对象创建动态 SQL 插入
- django - 使用 Django 框架时无法在 Bootstrap 中的行之间放置间距
- c++ - 将 unique_ptr 移到变体之外
- asp.net - 构建.net项目时jenkins/command中的错误MSB3202
- ios - 有没有办法在 GMSMapView 中添加路线注释?