空运算

Question

LedgerId    AccountId   EntryType   Debit       Credit      
2               2           D       50000.00    NULL        
3               2           D       10000.00    NULL        
4               2           C       NULL        25000.00    
6               2           C       NULL        10000.00    
7               2           D       89000.00    NULL        
8               2           D       89000.00    NULL        
10              3           D       715871.00   NULL        

下面的查询计算Balance

Select Accounts.ID [AccountID],Name,AccountType [AccountType], SUM(Debit) - SUM(Credit) [Balance] FROM Accounts 
    join Dealers on Accounts.DealerId = Dealers.ID 
    join Ledger on Accounts.ID = Ledger.AccountId
    GROUP BY Accounts.ID, Name, AccountType

它还会：

AccountID   Name    AccountType     Balance
2           Mateen  P               203000.00
3           Shery   P               NULL

预期产出：

AccountID   Name    AccountType     Balance
2           Mateen  P               203000.00
3           Shery   P               715871.00

帐户3的Balance是Null，当从null中减去任何内容时，它返回null。

例如，：

select 5 - NULL

返回NULL。

的问题：现在，我怎样才能得到Balance而不是NULL？

Answer 1

您可以使用coalesce

coalesce(sum(Debit), 0) - coalesce(sum(Credit), 0)

Answer 2

尝试使用isnull(value,0)，它将接受空值为0。

Select Accounts.ID [AccountID],Name,AccountType [AccountType], 
 SUM(isnull( Debit,0)) - SUM(isnull(Credit,0)) isnull([Balance],0) as 
   Balance FROM Accounts 
     join Dealers on Accounts.DealerId = Dealers.ID 
     join Ledger on Accounts.ID = Ledger.AccountId
GROUP BY Accounts.ID, Name, AccountType

Answer 3

如果存在空值问题，则SUM聚合函数将忽略空值。如果找到空值，合并函数将用0替换它。

    Select Accounts.ID [AccountID],
           Name,
           AccountType [AccountType], 
           SUM(coalesce(Debit,0)) - SUM(coalesce(Credit,0)) [Balance] 
FROM       Accounts 
           join Dealers on Accounts.DealerId = Dealers.ID 
           join Ledger on Accounts.ID = Ledger.AccountId
           GROUP BY Accounts.ID, Name, AccountType