如何使用聚合函数优化sql查询？

Question

下面是我想优化的sql查询。

 SELECT (ISNULL((SELECT SUM(Amount) FROM tblPayment WHERE TransactionType = 1 and AccountType = 2 and Status = 3),0) 
         +
         ISNULL((SELECT SUM(Amount) FROM tblPayment WHERE TransactionType = 2 and AccountType = 2 and Status = 3),0)
        ) 'Total Funds'

如何以更好的方式优化它？

谢谢你的建议！

Answer 1

不要对sum TransactionType = 1和TransactionType = 2有两个不同的查询，而要使用TransactionType in (1,2)

SELECT Isnull(Sum(Amount),0)
FROM   tblPayment
WHERE  TransactionType IN( 1, 2 )
       AND AccountType = 2
       AND Status = 3 

Answer 2

尝试：

SELECT COALESCE(SUM(Amount), 0) AS [Total Funds]
FROM tblPayment
WHERE AccountType = 2 AND Status = 3 
      AND TransactionType IN (1,2)