如何获得父母的总和？ [英] How to get total sum of parent child ?
问题描述
我们有两张桌子。使用这些表我们可以得到这个结果。使用单选查询。
< pre>账户表
AccountID名称父母
1标头100空白
2标头110 1
3儿童111 2
4儿童112 2
5儿童113 2
6儿童120 1
7标题130 1
8儿童131 7
9儿童132 7
10标题200 NULL
11标题210 10
12儿童221 10
13儿童220 10
14标题300空白
15儿童310 14
AccountLedger表
账户价值
3 50.00
3 10.00
3 50.00
3 80.00
3 40.00
4 20.00
4 10.00
4 100.00
5 80.00
5 90.00
5 60.00
6 100.00
8 40.00
8 70.00
8 10.00
8 40.00
9 20.00
12 60.00
12 30.00
12 70.00
13 10.00
13 100.00
15 30.00
15 90.00
15 40.00
需要输出： -
AccountId父名称值
1 NULL标题100 870.00
2 1标题110 590.00
3 2儿童111 230.00
4 2儿童112 130.00
5 2儿童113 2 30.00
6 1儿童120 100.00
7 1标题130 180.00
8 7儿童131 160.00
9 7儿童132 20.00
10 NULL标题200 270.00
11 10标题210 0.00
12 10儿童221 160.00
13 10儿童220 110.00
14空标题300 160.00
15 14儿童310 160.00
我尝试过：
;随着CTE1
AS
（
选择AccountId，AccountName，ParentId，null作为Accountvalue来自AccountList
Union All
选择A.AccountId，A.AccountName，A.ParentId， SUM（ISNULL（Accountvalue，0））来自AccountList A
内部加入Accountledger T On A.AccountId = T.Account GROUP BY AccountId，AccountName，ParentID
）
选择Accountid，AccountName，ParentId ，SUM（ISNULL（Accountvalue，0））as AccountValue
来自CTE1
Group By Accountid，AccountName，ParentId
ORDER BY AccountId - option（maxrecursion 0）
您的原始问题被告知如何获得父母的总和？ [ ^ ]在两次通过中执行此操作...
首先获得每个账户的个人金额，然后获得每个父母的子金额。如果您真的非常希望这是一个查询，那么只需使用多个CTE，例如; with cte1 AS
（
< span class =code-comment> - 每个帐户的个别金额总和
选择帐户，ISNULL（SUM（[值]）， 0 ） AS [Value]
来自 #Account A
LEFT OUTER JOIN #AccountLedger L ON L.Account = A.AccountID
group by 帐户
）
，cte2 AS
（
- 所有孩子的总和每个父母的金额
选择父，ISNULL（SUM（[Value]）， 0 ） AS [Value]
来自 #Account A
LEFT OUTER JOIN #AccountLedger L ON L.Account = A.AccountID
WHERE Parent IS NOT NULL
GROUP BY 父
）
选择 AccountID，A.Parent，[Name]，ISNULL（cte1。[Value]， 0 ）+ ISNULL（cte2。[Value]， 0 ）
来自 #account A
LEFT OUTER JOIN cte1 ON A.AccountID = cte1.Account
LEFT OUTER JOIN cte2 ON A.AccountID = cte2.Parent
要点注意事项：
在我的（临时）表中，我使用了示例数据中的列名和表名，而不是您发布的查询。在给我们提供样本数据时，使用实际的列名称是一个非常好的主意。我不想回去重命名。
注意SUM（ISNULL（账户价值，0））毫无意义。 SUM将忽略NULL值。但是，如果所有值都为NULL，则SUM将返回NULL，因此您需要使用ISNULL（SUM（AccountValue），0）
虽然我在cte1和cte2中都使用了该构造，但我仍然需要在尝试将值一起添加时检查NULLISNULL（cte1。[Value]，0）+ ISNULL （cte2。[Value]，0）因为我使用了LEFT OUTER JOIN - 即cte1。[Value]可能为NULL，因为cte1中没有该Id的行。
we have two tables. and using these table can we get this result.Using single select query. <pre>Account Table AccountID Name Parent 1 Header 100 NULL 2 Header 110 1 3 Child 111 2 4 Child 112 2 5 Child 113 2 6 Child 120 1 7 Header 130 1 8 Child 131 7 9 Child 132 7 10 Header 200 NULL 11 Header 210 10 12 Child 221 10 13 Child 220 10 14 Header 300 NULL 15 Child 310 14
AccountLedger Table Account Value 3 50.00 3 10.00 3 50.00 3 80.00 3 40.00 4 20.00 4 10.00 4 100.00 5 80.00 5 90.00 5 60.00 6 100.00 8 40.00 8 70.00 8 10.00 8 40.00 9 20.00 12 60.00 12 30.00 12 70.00 13 10.00 13 100.00 15 30.00 15 90.00 15 40.00
OUTPUT NEEDED :- AccountId Parent Name value 1 NULL Header 100 870.00 2 1 Header 110 590.00 3 2 Child 111 230.00 4 2 Child 112 130.00 5 2 Child 113 230.00 6 1 Child 120 100.00 7 1 Header 130 180.00 8 7 Child 131 160.00 9 7 Child 132 20.00 10 NULL Header 200 270.00 11 10 Header 210 0.00 12 10 Child 221 160.00 13 10 Child 220 110.00 14 NULL Header 300 160.00 15 14 Child 310 160.00
What I have tried:
;With CTE1 AS ( Select AccountId,AccountName, ParentId,null as Accountvalue From AccountList Union All Select A.AccountId, A.AccountName, A.ParentId,SUM(ISNULL(Accountvalue,0)) From AccountList A Inner Join Accountledger T On A.AccountId = T.Account GROUP BY AccountId,AccountName,ParentID ) Select Accountid, AccountName ,ParentId,SUM(ISNULL(Accountvalue,0)) as AccountValue From CTE1 Group By Accountid,AccountName, ParentId ORDER BY AccountId--option (maxrecursion 0)
As you were advised on your original question How to get total sum of parent child ?[^] do this in two passes...
Firstly get the individual amounts for each account, then get the total child amounts for each parent. If you really desperately want this to be a single query then just use multiple CTEs e.g.;with cte1 AS ( -- sum of individual amounts for each account select Account, ISNULL(SUM([Value]),0) AS [Value] from #Account A LEFT OUTER JOIN #AccountLedger L ON L.Account = A.AccountID group by Account ) ,cte2 AS ( -- Sum of all child amounts for each parent select Parent, ISNULL(SUM([Value]),0) AS [Value] from #Account A LEFT OUTER JOIN #AccountLedger L ON L.Account = A.AccountID WHERE Parent IS NOT NULL GROUP BY Parent ) select AccountID, A.Parent, [Name], ISNULL(cte1.[Value],0) + ISNULL(cte2.[Value],0) from #account A LEFT OUTER JOIN cte1 ON A.AccountID = cte1.Account LEFT OUTER JOIN cte2 ON A.AccountID = cte2.Parent
Points to note:
In my (temp) tables I used the column names and table names from your sample data not the query you posted. It's a really good idea to use the actual column names when giving us sample data. I wasn't inclined to go back and rename things.
Note thatSUM(ISNULL(Accountvalue,0))is pointless. SUM will ignore NULL values. However, if all the values are NULL then the SUM will return NULL so you need to useISNULL(SUM(AccountValue),0)
Although I have used that construct in both cte1 and cte2 I still need to check for NULLs when trying to add the values togetherISNULL(cte1.[Value],0) + ISNULL(cte2.[Value],0)because I have used LEFT OUTER JOIN - i.e. it is possible for cte1.[Value] to be NULL simply because there are no rows in cte1 for that Id.
这篇关于如何获得父母的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
