Простейшим способом является использование подвыбора (я назвал вашу таблицу foo
):
select
foo.*,
(select sum(revenue) from
foo ly
where
ly.yyyymm between foo.yyyymm - 100 and foo.yyyymm - 1
) as last_years_revenue
from foo;
Это дает следующий результат:
Ввод:
yyyymm | revenue
--------+---------
201701 | 450
201701 | 600
201702 | 350
201601 | 45
201601 | 60
201602 | 35
yyyymm | revenue | last_years_revenue
--------+---------+--------------------
201701 | 450 | 140
201701 | 600 | 140
201702 | 350 | 1085
201601 | 45 | NULL
201601 | 60 | NULL
201602 | 35 | 105
[ 116] Выручка 2016 года составляет 60 + 35 + 45 = 140.
Доход за 2017-02 гг. Составляет 450 + 600 + 35 = 1085.
Некоторые бренды базы данных (например, Oracle, Microsoft SQL Server) поддерживают дополнительный синтаксис SQL для выполнения "рекурсивных запросов", но MySQL не поддерживает никакое подобное решение.
Проблема, которую Вы описываете, совпадает с представлением древовидной структуры в базе данных SQL. У Вас просто есть длинное, тощее дерево.
Существует несколько решений для того, чтобы сохранить и выбрать этот вид структуры данных от RDBMS. Посмотрите некоторые следующие вопросы:
Так как Вы упоминаете, что требуется ограничить "глубину", возвращенную запросом, можно достигнуть этого при запросах списка этот путь:
SELECT * FROM mytable t1
LEFT JOIN mytable t2 ON (t1.next_id = t2.id)
LEFT JOIN mytable t3 ON (t2.next_id = t3.id)
LEFT JOIN mytable t4 ON (t3.next_id = t4.id)
LEFT JOIN mytable t5 ON (t4.next_id = t5.id)
LEFT JOIN mytable t6 ON (t5.next_id = t6.id)
LEFT JOIN mytable t7 ON (t6.next_id = t7.id)
LEFT JOIN mytable t8 ON (t7.next_id = t8.id)
LEFT JOIN mytable t9 ON (t8.next_id = t9.id)
LEFT JOIN mytable t10 ON (t9.next_id = t10.id);
Это будет работать как патока, и результат возвратится все на одной строке (на связанный список), но Вы получите результат.
Это не столько решение, сколько обходной путь, но для линейного списка (а не дерева, упомянутого Билл Карвин), может быть более эффективно использовать столбец сортировки для ваш список. Например:
TABLE `schema`.`my_table` (
`id` INT NOT NULL PRIMARY KEY,
`order` INT,
data ..,
INDEX `ix_order` (`sort_order` ASC)
);
То:
SELECT * FROM `schema`.`my_table` ORDER BY `order`;
Это имеет недостаток, заключающийся в более медленной вставке (вы должны переместить все отсортированные элементы за точку вставки), но должен быть быстрым для извлечения, потому что столбец порядка индексируется.