总共有两列不工作

Question

我想从慈善金额中得到总数，慈善金额是0减去慈善金额1的和。

SELECT SUM(Amount) - (SELECT SUM(Amount) 
                        FROM Transactions 
                       WHERE (Charity = 1))
  FROM Transactions 
 WHERE (Charity = 0)

此查询运行，但不会给我正确的结果。

Answer 1

将所有值视为正数：

SELECT SUM(ABS(Amount)) - (SELECT SUM(ABS(Amount)) 
                        FROM Transactions 
                       WHERE (Charity = 1))
  FROM Transactions 
 WHERE (Charity = 0)

但负面交易是否应该被视为正面交易，这一点令人严重怀疑。如果此表中存在合法的负值事务，则可能应将它们视为负值(与原始查询中一样)，或将其排除在外：

SELECT SUM(Amount) - (SELECT SUM(Amount) 
                        FROM Transactions 
                       WHERE (Charity = 1) AND Amount > 0)
  FROM Transactions 
 WHERE (Charity = 0) AND Amount > 0

不确定sqlite中有哪些可用的工具。在标准SQL中，我可能会将其重写为：

SELECT
    COALESCE(SUM(CASE WHEN Charity=0 THEN Amount END),0) -
    COALESCE(SUM(CASE WHEN Charity=1 THEN Amount END),0)
FROM Transactions
WHERE
    Charity in (0,1) AND
    Amount > 0

(或用于ABS版本的类似转换)

Answer 2

如果这有帮助，请尝试。

select a.sum1 - b.sum2
from
(SELECT  SUM(Amount) as sum1 FROM Transactions WHERE (Charity = 0)) A,
(SELECT  SUM(Amount) as sum2 FROM Transactions WHERE (Charity = 1)) B

Answer 3

尝试下面的(我希望这在SQLite中有效)：

SELECT (SELECT  SUM(Amount) FROM Transactions WHERE (Charity = 0)) - 
       (SELECT  SUM(Amount) FROM Transactions WHERE (Charity = 1))