我写了一个查询,它将从名为allotment
:的表中的现有周期生成子周期
select a.product_id,
daterange((lower(a.allotment_period) + concat(n.i - 1, ' days')::interval)::date,
(upper(a.allotment_period) + concat(n.i, ' days')::interval)::date, '[]')
from test.allotment as a
cross join(select * from generate_series(1, a.period_length)) as n(i)
where a.id = 2
我得到了这个错误:
ERROR: invalid reference to FROM-clause entry for table "a" LINE 5: cross `join(select * from generate_series(1, a.period_length)... ^ HINT: There is an entry for table "a", but it cannot be referenced from this part of the query. SQL state: 42P01 Character: 250`
我发现了一个类似的SQL联接问题,"表有一个条目,但不能引用",但它并不能解决我的问题。有人能解决这个问题吗?
问题出在from
子句中。在Postgres的最新版本中,您可以使用:
from test.allotment a cross join lateral
generate_series(1, a.period_length) n(i)
在旧版本中,可以将generate_series()
包含在select
:中
from (select a.*, generate_series(1, a.period_length) as i
from test.allotment a
) a