在SQL查询中添加新列

Question

我想使用查询的结果创建新的列，以便找到运费与收入的变化。

最初的问题是，额外10%的运费对收入的影响是什么？我目前正在使用Northwind数据集。

select sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight*1.1) as New,  sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight) as Old, New-Old
from "Order Details", Orders;

SQL查询不工作，因为它不识别新变量“新建”和“旧”

我已经更新了暗示的版本，但是它会产生错误混乱。

NexusDB: Query534984250: Query execution failed: 
NexusDB: Correlation name for subquery expected:

SELECT New,Old,New-Old
FROM  (select
        sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight*1.1) as New,
        sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight) as Old
            FROM "Order Details"
               INNER JOIN  "Orders"  ON ("Order Details".OrderID = Orders.OrderID)
      )  ;

Answer 1

在子查询中运行列新和旧列后，可以使用它们

但是，您的(子)查询不会给出正确的结果

From `Order Details`, `Orders`

是两个表之间的交叉连接，返回的行太多，无法给出预期的结果。

所以你会

FROM `Order Details` od 
    INNER JOIN  `Orders` o ON o.id = od.order_REf_id

然后你仍然必须检查结果是否是你想要的

SELECT New,Old, New-Old
FROM  (select 
        sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight*1.1) as New,
        sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight) as Old
        from `Order Details`, `Orders`
      ) t1

在最后的效果，它会像

SELECT New,Old, New-Old
FROM  (select 
        sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight*1.1) as New,
        sum(UnitPrice*Quantity*(1-Discount))  - sum(Freight) as Old
            FROM `Order Details` od 
               INNER JOIN  `Orders` o ON o.id = od.order_REf_id`
      ) t1