PostgreSQL:在同一查询中使用计算列 [英] PostgreSQL: using a calculated column in the same query

问题描述

在postgres中使用计算列时遇到麻烦.下面给出了在SQL中工作的类似代码，是否可以在PostgreSQL中重新创建它?

select cost_1, quantity_1, cost_2, quantity_2, 
      (cost_1 * quantity_1) as total_1,
      (cost_2 * quantity_2) as total_2,
      (calculated total_1 + calculated total_2) as total_3
from data;

在PostgreSQL中，类似的代码返回以下错误:

列total_1和total_2不存在.

解决方案

您需要将SELECT语句包装到派生表中，以便能够访问列别名:

select cost1,
       quantity_1,
       cost_2,
       quantity_2
       total_1 + total_2 as total_3
from (
    select cost_1, 
           quantity_1, 
           cost_2, 
           quantity_2, 
           (cost_1 * quantity_1) as total_1,
           (cost_2 * quantity_2) as total_2
    from data
) t

对此不会有任何性能损失.

(我真的对您的原始SQL语句完全在DBMS中运行感到非常惊讶)

I am having trouble using a calculated column in postgres. A similar code which works in SQL is given below, is it possible to recreate this in PostgreSQL?

select cost_1, quantity_1, cost_2, quantity_2, 
      (cost_1 * quantity_1) as total_1,
      (cost_2 * quantity_2) as total_2,
      (calculated total_1 + calculated total_2) as total_3
from data;

In PostgreSQL a similar code returns the error that:

the column total_1 and total_2 do not exist.

解决方案

You need to wrap the SELECT statement into a derived table in order to be able to access the column alias:

select cost1,
       quantity_1,
       cost_2,
       quantity_2
       total_1 + total_2 as total_3
from (
    select cost_1, 
           quantity_1, 
           cost_2, 
           quantity_2, 
           (cost_1 * quantity_1) as total_1,
           (cost_2 * quantity_2) as total_2
    from data
) t

There won't be any performance penalty on that.

(I'm really surprised that your original SQL statement runs at all in a DBMS)

这篇关于PostgreSQL:在同一查询中使用计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！