MySQL递归减去和乘以分组的值 [英] Mysql recursive substracting and multiplying grouped values

问题描述

用语言无法真正解释我的问题，但是通过一个例子，我可以清楚地说明它:

Couldn't really explain my problem with words, but with an example I can show it clearly:

我有一个这样的表:

id    num   val  flag
0     3     10   1
1     5     12   2
2     7     12   1
3     11    15   2

我想遍历所有行，计算出"num"的增加量，然后将该差值乘以"val"值.当我计算所有这些结果时，我想将这些结果加在一起，但要根据标志"值分组.

And I want to go through all the rows, and calculate the increase of the "num", and multiply that difference with the "val" value. And when I calculated all of these, I want to add these results together, but grouped based on the "flag" values.

这是我要在桌子上运行的数学方程式:

This is the mathematical equation, that I want to run on the table:

Result_1 = (3-0)*10 + (7-3)*12
Result_2 = (5-0)*12 + (11-5)*15

78  = Result_1
150 = Result_2

谢谢.

推荐答案

有趣的问题.不幸的是，MYSQL不支持recursive queries，因此您需要在此处有所创意.这样的事情可能会起作用:

Interesting question. Unfortunately MYSQL doesn't support recursive queries, so you'll need to be a little creative here. Something like this could work:

select flag,
  sum(calc)
from (
  select flag, 
    (num-if(@prevflag=flag,@prevnum,0))*val calc,
    @prevnum:=num prevnum,
    @prevflag:=flag prevflag
  from yourtable 
    join (select @prevnum := 0, @prevflag := 0) t
  order by flag
  ) t
group by flag

• SQL小提琴演示
• SQL Fiddle Demo

这篇关于MySQL递归减去和乘以分组的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！