sql - 对行进行递归操作的 Postgres 查询

Question

create table foo (a int, b float);

insert into foo values (1, 2), (2,3),(3,2.5),(4,1.5);

 a |  b  
---+-----
 1 |   2
 2 |   3
 3 | 2.5
 4 | 1.5

我想计算每个 b 对于任何 a

的差异

select RECURSIVE diff (?,?) on b where a=1

输出:

 a |  diff  
---+-----
 1 |   0
 2 |   1
 3 | .5
 4 | -.5

是否可以对所有表格行递归应用一个函数？

Answer 1

select b - b0
from
    foo
    cross join
    (select b as b0 from foo where a = 1) s
;
 ?column? 
----------
        0
        1
      0.5
     -0.5

由于评论中有成千上万的请求，这是优雅的版本:

select f1.b - f2.b
from
    foo f1
    cross join
    foo f2
where f2.a = 1