sql-server - 根据列中的值对行组求和

Question

我是 SQL 新手，我有下表

我需要在下面选择并获得所需的结果。

标签： sql-servertsql

Answer 1

尝试如下查询。

select  
    [Account Group] =AG.Name,
    [Ledger Name]   =AL.Name,
    DR  =SUM(CASE WHEN TT.Name='DR' THEN Amount ELSE NULL END),-- case based SUM expression
    CR  =SUM(CASE WHEN TT.Name='CR' THEN Amount ELSE NULL END)
from Account_Group AG
join Account_ledgers AL -- NOTE  we have used INNER JOIN throughout
    on AG.Id=AL.AccountGroupId
join Transactions  T
    on T.AccountLedgerId=AL.Id
join Transaction_Type TT
    on TT.Id= T.TransactionTypeId
group by AG.Name,AL.Name -- Group by contains all the things we need in SELECT list