子查询值的年度变化 [英] annual change of values with subquery

问题描述

此查询给出1093错误:

This query gives a 1093 error:

UPDATE values_table as outer_select 
    SET annual_change = sample_value - (
        SELECT sample_value FROM values_table 
        WHERE date_sampled = DATE_SUB(outer_select.date_sampled, INTERVAL 1 YEAR)
    );

我试图将每行的annual_change设置为等于当前行的sample_value减去上一年的sample_value.

I'm trying to set annual_change for every row equal to the current row's sample_value less last year's sample_value.

数据不会返回到开始时间，那么如何处理缺乏历史价值的问题呢?

The data doesn't go back to the beginning time, so how can the lack of historical values also be handled?

推荐答案

尝试一下

UPDATE values_table as a
join  values_table as b 
ON b.date_sampled = DATE_SUB(a.date_sampled, INTERVAL 1 YEAR)
set a.annual_change = a.sample_value - b.sample_value

这篇关于子查询值的年度变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！