根据上一行结果查找每个数量的值 [英] Find Value per quantity based on Above row result

问题描述

我有下表称为温度".

S A B C D

S A B C D

1 10 6 0 6(因为它是第一行，我们将分配B值)

1 10 6 0 6(Since it is first row we will assign B value)

2 25 7 10 =((25 * 7)+(10 * 6))/(25 + 10)= 6.71

2 25 7 10 =((25*7)+(10*6))/(25+10)=6.71

3 5 11 35 =((5 * 11)+(35 * 6.71))/(5 + 35)= 7.24

3 5 11 35 =((5*11)+(35*6.71))/(5+35)=7.24

4 10 8 30 =((10 * 8)+(30 * 7.24))/(10 + 30)= 7.43

4 10 8 30 =((10*8)+(30*7.24))/(10+30)=7.43

有人可以告诉我如何用SQL编写逻辑吗?从第二行可以看到它使用的是第一行的结果.

Can Someone tell me how we can write the logic in SQL?from the Second row you can see it is using the result of the first row.

推荐答案

也许有更好的方法，但是您可以使用递归CTE:

There might be a better way, but you can use a recursive CTE:

with recursive cte as (
      select s, a, b, c, b as d
      from t
      where s = 1
      union all
      select t.s, s.a, t.b, t.c,
             ( (t.a*t.b) + (t.c*cte.d) ) / (t.a + t.c)
      from cte join
           t
           on t.s = cte.s + 1
    )
select *
from cte;

这篇关于根据上一行结果查找每个数量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！