MySQL - 想要从两个 SELECT 语句中减去两个 SUM() 值

Question

我希望能够从两个 SELECT 查询中获取我在 SUM() 中生成的两个值，然后减去这些值以获得结果 (OutstandingFunds)。

这是我的两个 SELECT 查询:

声明(一):

SELECT SUM(Cf.Amount) AS ClearedFunds
FROM (
SELECT Amount FROM PAYMENT1 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT2 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT3 WHERE `Status` = "Cleared") AS Cf;

声明(2):

SELECT SUM(Price) AS TotalSales
FROM PROPERTY
WHERE Status = “Sold”;

谢谢你的时间

Answer 1

如果您不需要显示总销售额和已清算资金的单独小计，您可以这样做:

SELECT SUM(Total.`Price`) AS ClearedFunds
FROM (
    SELECT `Price` FROM PROPERTY WHERE `Status` = 'Sold'
    UNION ALL
    SELECT (`Amount` * -1) AS `Price` FROM PAYMENT1 WHERE `Status` = 'Cleared'
    UNION ALL
    SELECT (`Amount` * -1) AS `Price` FROM PAYMENT2 WHERE `Status` = 'Cleared'
    UNION ALL
    SELECT (`Amount` * -1) AS `Price` FROM PAYMENT3 WHERE `Status` = 'Cleared'
) AS Total;

我假设您想要从这里的总销售额中减去已清算的资金。