sql - 获取父子层次结构中的总计和小计

Question

我有以下表格结构，我试图获取其总计和小计并显示值的汇总。

ChartOfAccounts(AccountNumber, AccountDescription, ParentAccountNumber, IsControlAccount)
Ledger(LedgerId, JournalId, AccountNumber, IsDebit, Amount)

我已设法使用 CTE 获取所需的父子关系，但不确定如何使用此获取控制帐户余额并将其汇总到父帐户中。

到目前为止，我已经成功地将以下查询放在一起，但这并不完全是我想要的 --> SQL Fiddle 。当前查询似乎没有正确汇总和分组父子总计。 (我已从 fiddle 中排除了年、月列)

描述该问题的另一种方式是，所有控制帐户都应具有其子帐户的总数。

我需要的输出如下 (年、月、帐号、帐户描述、借记余额、贷记余额、余额)

|Account#|Acc Desc                                 | DR     | CR     | BAL    |
|1000    |Accounts Receivable                      |10000   |5000    |5000    |
|1200    |Buyer  Receivables                       |5000    |0       |5000    |
|12001   |Buyer  Receivables - Best Buy            |5000    |0       |5000    |
|1500    |Offers                                   |5000    |5000    |0       |
|4000    |Accounts Payable                         |        |4475.06 |4475.06 |  
|4100    |Supplier Invoice Payables                |        |4475.06 |4475.06 |  
|41002   |Supplier Invoice Payables - Knechtel     |        |4475.06 |4475.06 |  
|6000    |Revenue                                  |        |524.93  |524.93  |  
|6100    |Membership Fees Revenue                  |        |        |0       |  
|6200    |Processing Fees Revenue                  |        |100     |100     |  
|62002   |Processing Fees Revenue - Knechtel       |        |100     |100     |  
|6300    |Fees Revenue                             |        |424.93  |424.93  |  
|63002   |Fees Revenue  - Knechtel                 |        |424.93  |424.93  |  

Answer 1

这是我想出的并且能够非常接近匹配您所需的输出

WITH CTEAcc 
AS
(
    SELECT
        coa.accountDescription,coa.accountnumber,coa.accountnumber as parentaccount
        FROM ChartOfAccounts coa
  where iscontrolaccount=1
  union all select c.accountdescription, coa.accountnumber, c.ParentAccount 
    from chartofaccounts coa
    inner join cteacc c on coa.ParentAccountNumber=c.accountnumber

)

select parentaccount as [Account#], accountdescription as [Acc Desc], 
sum(case when isdebit=1 then amount else 0 end) as DR,
sum(case when isdebit=0 then amount else 0 end) as CR,
sum(case when isdebit=1 then amount else 0 end)-sum(case when isdebit=0 then amount else 0 end) as BAL
from (select c.accountdescription, c.accountnumber, 
      c.parentaccount, l.isdebit, l.amount 
      from cteacc c
left join ledger l
  on c.accountnumber=l.accountnumber
union all select c.accountdescription, 
      c.accountnumber, c.accountnumber as parentaccount, 
      l.isdebit, l.amount 
      from ChartOfAccounts c
      inner join ledger l
  on c.accountnumber=l.accountnumber where amount<>0) f
group by parentaccount, accountdescription
order by parentaccount

这是sql fiddle :http://www.sqlfiddle.com/#!3/d94bc/106