减去mysql中两个特定行的值

xzlaal3s 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(419)

我正在通过mysql进行计算。下一个值是日值，现在我想看看最后一行与前一天（=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。列“value”不能为空。有人能帮我找到解决办法吗？谢谢！
编辑：今天回答一个问题

mysql Parameters Math

来源：https://stackoverflow.com/questions/51944978/minus-the-values-of-2-specific-rows-in-mysql

1条答案

按热度按时间

vddsk6oq1#

一 SHOW 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 ;

如果需要在事件中使用复合语句，则需要将它们放在begin/end下面

赞(0）回复(0）举报 2021-06-20

我来回答

减去mysql中两个特定行的值

1条答案

相关问题

热门标签

最新问答