MySQL:使用两列的值计算第三列

Question

我确信这是一个常见问题，但我无法按照我描述的方式找到解决方案。我的查询是这样的:

SELECT 
    (
        SELECT SUM(finance_transaction_components.invoiced_price) 
        FROM finance_transactions
        JOIN finance_transaction_components ON finance_transaction_components.transaction_id = finance_transactions.id
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    ) AS "Total Costs",

    (
        SELECT SUM(finance_transactions.agent_price)
        FROM finance_transactions
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE finance_transactions.product_id != 10
        AND DATE_FORMAT(completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29" 
    ) AS "Total Revenue",

    (
        SELECT SUM(finance_transactions.agent_price)
        FROM finance_transactions
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE finance_transactions.product_id != 10
        AND DATE_FORMAT(completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29" 
        AND IF(finance_transactions.lldrg > 0, IF(finance_transactions.lldrg = 1, 0, 1) ,1) = 1
    ) - ( SELECT SUM(finance_transaction_components.invoiced_price) 
        FROM finance_transactions
        JOIN finance_transaction_components ON finance_transaction_components.transaction_id = finance_transactions.id
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    )   AS "Margin"

FROM finance_transactions
GROUP BY finance_transactions.agent_id

列是什么并不重要。在这种情况下，重要的是: margin 列需要重复总成本> 和总收入查询来计算，这似乎是一种愚蠢的做法。但显然我不能只说“总收入”-“总成本”AS “ margin 。

Answer 1

只需使用内部查询即可实现相同的效果，而无需重复代码:

SELECT TotalCosts, TotalRevenue, TotalCost - TotalRevenue AS Margin
FROM 
(
    SELECT 
    (
        SELECT SUM(finance_transaction_components.invoiced_price) 
        FROM finance_transactions
        JOIN finance_transaction_components ON finance_transaction_components.transaction_id = finance_transactions.id
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE DATE_FORMAT(finance_transactions.completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29"
    ) AS TotalCosts,

    (
        SELECT SUM(finance_transactions.agent_price)
        FROM finance_transactions
        JOIN agents ON agents.id = finance_transactions.agent_id
        WHERE finance_transactions.product_id != 10
        AND DATE_FORMAT(completed_date, "%Y-%m-%d") BETWEEN "2013-10-01" AND "2013-10-29" 
    ) AS TotalRevenue
) t

编辑:这个答案基于问题的第一个版本，即具有聚合函数的版本。