php - 包含 AS 的 SQL SELECT 语句

Question

我有 SQL 查询:

select title, scale / next_scale, c
from ( select title, scale, scale*D0 AS c, 
              lead(scale) over(partition by title order by scale asc) as next_scale,
              row_number() over(partition by title order by scale asc) as agg_row
       from signatures
     ) agg
where agg_row = 1;

它按预期工作。但是，我真正希望排序的“比例”值是几列之间的算术运算，所以我尝试使用 AS 子句(如上所示)并将查询修改为:

select title, scale / next_scale, c
from ( select title, scale, scale*D0 AS c, 
              lead(scale) over(partition by title order by c asc) as next_scale,
              row_number() over(partition by title order by c asc) as agg_row
       from signatures
     ) agg
where agg_row = 1;

但是，它在 ORDER BY c 处失败。为什么是这样？我可以用 ORDER BY scale*D0 代替，效果很好。但是，我最终会想使用这样的术语:scale*D0*D1*D2*...*D100；而且我不想计算 3 次不同的时间——更不用说查询的物理长度了。我希望有 scale*D0*D1*D2*...*D100 AS c 然后 ORDER BY c。

这可能吗？

我正在使用 PostgreSQL。

非常感谢，布雷特

Answer 1

在子查询中计算c:

select title, scale / next_scale, c
from ( select title, scale, c, 
              lead(scale) over(partition by title order by c asc) as next_scale,
              row_number() over(partition by title order by c asc) as agg_row
       from (select title, scale, scale * D0 AS c from signatures) signatures_calc
     ) agg
where agg_row = 1;