在父子层次结构中获取总计和子总计

Question

我有以下表结构，想要获取总计和小计，并显示值的汇总。

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

我已成功使用CTE获取所需的父子关系，但不确定如何使用它来获取汇总为父帐户的控制帐户余额。到目前为止，我已经组合了以下查询，但并不完全符合我的要求 -> SQL Fiddle。当前查询似乎无法正确汇总和分组父子总数（我已从fiddle中排除了year、month列）。

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

我需要的输出是以下内容 (年，月，AccountNumber，AccountDescription，DebitBalance，CreditBalance，Balance)。

|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  |  

- Ahmad

---

你写道：“我需要的输出如下”，并发布了一个示例输出。但是我在其中找不到任何“总计和小计”。那么输出表格应该长什么样呢？ - Avt

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

- attila

Answer 2

又一种变化。仅为参考保留了层次结构和iscontrol字段。首先将每个帐户与帐户层次结构关联（递归CTE）。然后，对于每个帐户，根据层次结构位置（以及是否为控制帐户）计算帐户的总账目金额。最后，包装在另一个查询中计算余额并从输出中剥离未使用的帐户。

WITH AccountHierarchy AS (

    SELECT AccountNumber
          ,AccountDescription
          ,CAST(AccountNumber AS VARCHAR(MAX))
             + '/' AS AccountHierarchy
          ,IsControlAccount
      FROM ChartOfAccounts
      WHERE ParentAccountNumber IS NULL

    UNION ALL

    SELECT c.AccountNumber
          ,c.AccountDescription
          ,CAST(h.AccountHierarchy  AS VARCHAR(MAX))
             + CAST(c.AccountNumber AS VARCHAR(MAX))
             + '/' AS AccountHierarchy
          ,c.IsControlAccount
      FROM ChartOfAccounts c
      INNER JOIN AccountHierarchy h ON (c.ParentAccountNumber = h.AccountNumber)
      WHERE ParentAccountNumber IS NOT NULL
)

SELECT AccountNumber
      ,AccountDescription
      ,AccountHierarchy
      ,IsControlAccount
      ,DR
      ,CR
      ,CASE WHEN (DR IS NULL AND CR IS NULL) THEN NULL
            ELSE COALESCE(DR, 0) - COALESCE(CR, 0)
            END AS BAL

  FROM (SELECT h.AccountNumber
              ,h.AccountDescription
              ,h.AccountHierarchy
              ,h.IsControlAccount

              ,(SELECT SUM(l.Amount)
                  FROM Ledger l
                  INNER JOIN AccountHierarchy hd ON (l.AccountNumber = hd.AccountNumber)
                  WHERE l.IsDebit = 1
                    AND (    (h.IsControlAccount = 1 AND hd.AccountHierarchy LIKE h.AccountHierarchy + '%')
                          OR hd.AccountHierarchy = h.AccountHierarchy)
               ) AS DR

              ,(SELECT SUM(l.Amount)
                  FROM Ledger l
                  INNER JOIN AccountHierarchy hd ON (l.AccountNumber = hd.AccountNumber)
                  WHERE l.IsDebit = 0
                    AND (    (h.IsControlAccount = 1 AND hd.AccountHierarchy LIKE h.AccountHierarchy + '%')
                          OR hd.AccountHierarchy = h.AccountHierarchy)
               ) AS CR

          FROM AccountHierarchy h
        ) x

  WHERE NOT(CR IS NULL AND DR IS NULL)
  ORDER BY AccountHierarchy

我使用这个问题作为层级结构的示例。

输出：

|        ACCOUNTNUMBER |                 ACCOUNTDESCRIPTION |                                                ACCOUNTHIERARCHY | ISCONTROLACCOUNT |     DR |        CR |        BAL |
|----------------------|------------------------------------|-----------------------------------------------------------------|------------------|--------|-----------|------------|
| 1000                 |                Accounts Receivable |                                           1000                / |                1 |  10000 |      5000 |       5000 |
| 1200                 |                 Buyer  Receivables |                      1000                /1200                / |                1 |   5000 |    (null) |       5000 |
| 12001                |      Buyer  Receivables - Best Buy | 1000                /1200                /12001               / |                0 |   5000 |    (null) |       5000 |
| 1500                 |                             Offers |                      1000                /1500                / |                0 |   5000 |      5000 |          0 |
| 4000                 |                   Accounts Payable |                                           4000                / |                1 | (null) | 4475.0685 | -4475.0685 |
| 4100                 |                 Supplier  Payables |                      4000                /4100                / |                1 | (null) | 4475.0685 | -4475.0685 |
| 41002                |      Supplier  Payables - Knechtel | 4000                /4100                /41002               / |                0 | (null) | 4475.0685 | -4475.0685 |
| 6000                 |                            Revenue |                                           6000                / |                1 | (null) |  524.9315 |  -524.9315 |
| 6200                 |            Processing Fees Revenue |                      6000                /6200                / |                1 | (null) |       100 |       -100 |
| 62002                | Processing Fees Revenue - Knechtel | 6000                /6200                /62002               / |                0 | (null) |       100 |       -100 |
| 6300                 |                       Fees Revenue |                      6000                /6300                / |                1 | (null) |  424.9315 |  -424.9315 |
| 63002                |            Fees Revenue - Knechtel | 6000                /6300                /63002               / |                0 | (null) |  424.9315 |  -424.9315 |

- Glenn

Answer 3

从您想要的输出开始，我提出了以下查询，根据ParentAccountNumber对子帐户进行分组。子查询仅在我假设您想在求和之前将任何NULL值转换为0时才需要（在SQL中，NULL+ 42 = NULL）。

with preresult as
(
    select acc.ParentAccountNumber as AccountNumber,
          acc.AccountDescription as "Acc Desc",
          ISNULL(ld.Amount, 0) as DR,
          ISNULL(lc.Amount, 0) as CR

    from ChartOfAccounts acc

    left outer join Ledger ld
    on (ld.AccountNumber = acc.AccountNumber AND ld.IsDebit = 1)

    left outer join Ledger lc
    on (lc.AccountNumber = acc.AccountNumber AND lc.IsDebit = 0)

    where acc.ParentAccountNumber is not null
)

select c.AccountNumber as "ACC",
      c.AccountDescription as "ACC DESC",
      sum(DR) as DR,
      sum(CR) as CR,
      sum(DR) - sum(CR) AS BL

from preresult p
join ChartOfAccounts c on (c.AccountNumber = p.AccountNumber)

group by c.AccountNumber, c.AccountDescription;

这里可以找到sqlfiddle：http://www.sqlfiddle.com/#!3/d94bc/81/0

- perl

Answer 4

这似乎能够给你想要的东西：

;WITH recurs
AS
(
    SELECT C.AccountNumber, C.IsControlAccount, C.ParentAccountNumber, C.AccountDescription, 
            COALESCE((SELECT SUM(Amount) FROM Ledger WHERE AccountNumber = C.AccountNumber and IsDebit = 1), 0) AS DR,
            COALESCE((SELECT SUM(Amount) FROM Ledger WHERE AccountNumber = C.AccountNumber and IsDebit = 0), 0) AS CR,
            COALESCE((SELECT SUM(CASE WHEN IsDebit = 0 THEN Amount * -1 ELSE Amount END) FROM Ledger WHERE AccountNumber = C.AccountNumber), 0) AS BAL
    FROM ChartOfAccounts C
    WHERE IsControlAccount = 0
    UNION ALL
    SELECT C.AccountNumber, C.IsControlAccount, C.ParentAccountNumber, C.AccountDescription,
            r.DR, r.CR, R.BAL
    FROM ChartOfAccounts C
    INNER JOIN recurs r
        ON r.ParentAccountNumber = c.AccountNumber
)
SELECT R.AccountNumber, R.AccountDescription, SUM(R.DR) AS DR, SUM(R.CR) AS CR, SUM(R.BAL) AS BAL
FROM recurs R
WHERE NOT (R.DR = 0 AND R.CR = 0 AND R.BAL = 0)
GROUP BY R.AccountNumber, R.AccountDescription
ORDER BY AccountNumber

SQL Fiddle 在这里

结果：

|        ACCOUNTNUMBER |                 ACCOUNTDESCRIPTION |    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 |     0 | 4475.0685 | -4475.0685 |
| 4100                 |                 Supplier  Payables |     0 | 4475.0685 | -4475.0685 |
| 41002                |      Supplier  Payables - Knechtel |     0 | 4475.0685 | -4475.0685 |
| 6000                 |                            Revenue |     0 |  524.9315 |  -524.9315 |
| 6200                 |            Processing Fees Revenue |     0 |       100 |       -100 |
| 62002                | Processing Fees Revenue - Knechtel |     0 |       100 |       -100 |
| 6300                 |                       Fees Revenue |     0 |  424.9315 |  -424.9315 |
| 63002                |            Fees Revenue - Knechtel |     0 |  424.9315 |  -424.9315 |

- Steve Ford

---

如果你需要一个非负的 Bal（根据你的答案），你可以简单地在 BAL 上添加 ABS()。 - Steve Ford