mysql - 在一个查询中添加两个 Sql 查询的结果？

Question

基本上我有 2 个返回所需结果的查询

但我需要将这两个结果添加到“子查询”(如果那是一个术语)。

下面是两个有效的查询:

SELECT SUM(Amount) AS Amount FROM AccountTransactions
INNER JOIN AccountTransactionDocuments
ON AccountTransactions.AccountTransactionDocumentId = AccountTransactionDocuments.Id
where AccountTransactionDocuments.Date > '{Start}' and AccountTransactionDocuments.Date < '{End}' and AccountTransactions.Name = 'Cash Payment';

和

SELECT SUM(case when TenderedAmount > 0 then TenderedAmount else 0 end) AS TenderedAmount FROM Payments
where Payments.Date > '{Start}' and Payments.Date < '{End}' and  Payments.Name = 'Cash' ;

这是我尝试过的副本。我不知道在最后的 FROM 中放什么

SELECT
       (SELECT SUM(case when TenderedAmount > 0 then TenderedAmount else 0 end) AS TenderedAmount FROM Payments
where Payments.Date > '{Start}' and Payments.Date < '{End}' and  Payments.Name = 'Cash')
       +
       (SELECT SUM(Amount) AS Amount FROM AccountTransactions
INNER JOIN AccountTransactionDocuments
ON AccountTransactions.AccountTransactionDocumentId = AccountTransactionDocuments.Id
where AccountTransactionDocuments.Date > '{Start}' and AccountTransactionDocuments.Date < '{End}' and AccountTransactions.Name = 'Cash Payment') 
AS Cash Drawer 
FROM Payments;

有什么想法吗？？或者这甚至可能吗？

编辑:导致语法错误的是 AS Cash Drawer 中的空格。还删除了最后一个 FROM，它工作得很好，谢谢

Answer 1

如果您的 top 和 bottom 查询有效，您可以使用 union all 将它们合并在一起，然后将这 2 个结果相加。我将底部查询中的投标金额更改为金额，以便将其联系在一起。

    SELECT 
    SUM(amount)
FROM
    (SELECT 
        SUM(Amount) AS amount
    FROM
        AccountTransactions
    INNER JOIN AccountTransactionDocuments ON AccountTransactions.AccountTransactionDocumentId = AccountTransactionDocuments.Id
    WHERE
        AccountTransactionDocuments.Date > '{Start}'
            AND AccountTransactionDocuments.Date < '{End}'
            AND AccountTransactions.Name = 'Cash Payment' 
    UNION ALL SELECT 
        SUM(CASE
                WHEN TenderedAmount > 0 THEN TenderedAmount
                ELSE 0
            END) AS amount
    FROM    
        Payments
    WHERE
        Payments.Date > '{Start}'
            AND Payments.Date < '{End}'
            AND Payments.Name = 'Cash') AS amounts