我想使用查詢的結果來創建新列，以便找到運費到收入的變化
最初的問題是運費增加 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 查詢不起作用，因為它無法識別新變數“New”和“Old”
我已經更新了建議的版本，但它會生成錯誤訊息
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)
) ;
uj5u.com熱心網友回復：
您可以在子查詢中運行后使用列 New 和 Old
但是你的（子）查詢不會給你一個正確的結果
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/336029.html
上一篇：提交而不是回滾？