求和的层次结构查询 [英] Hierarchyal Query for Sum
问题描述
我有两个表一个是主法案(Act_Code,Name,Parent_Act)
和第二个是TransactionTable(Act_Code,Amt_Charge)。
有5个级别的帐户,意味着层次结构最多为5级。我需要交易中所有账户的总和以及每个父账户的适当金额。
喜欢
< pre lang =text>主表
Act_Code名称Parent_Act
1 |资产| 1
2 |现金| 1
3 | A / Rcv | 1
4 | PettyCash | 2
5 | ShopPettyCash | 4
6 | CashforShpVisitor | 5
|
7 |银行| 1
8 | PakBank | 7
9 | HBL | 8
10 | HBL Lhr | 9
11 HBL Gulberg | 10
12 | ABL Lahr | 8
TransctionTable
Act_Code Charge_Amt
6 | 1000
11 | 15000
6 | 2000
11 | 2500
12 | 16000
结果
Act_Code名称总和
1 |资产| 36500
2 |现金| 3000
3 | A / Rcv | 0
4 | PettyCash | 3000
5 | ShopPettyCash | 3000
6 | CashforShpVisitor | 3000
7 |银行| 33500
8 | PakBank | 33500
9 | HBL | 17500
10 | HBL Lhr | 17500
11 | HBL Gulberg | 17500
12 | ABL Lahr | 16000
创建 TABLE Master
([Act_Code] int ,[Name] varchar ( 17 ),[Parent_Act] int )
;
INSERT INTO Master
([Act_Code],[Name] ],[Parent_Act])
VALUES
( 1 , 资产', null ),
( 2 ,' 现金', 1 ),
( 3 ,' A / Rcv', 1 ),
( 4 ,' PettyCash', 2 ),
( 5 ,' ShopPettyCash', 4 ),
( 6 ,' CashforShpVisitor', 5 ),
( 7 ,' 银行', 1 ),
( 8 ,' PakBank', 7 ),
(< span class =code-digit> 9 ,' HBL', 8 ),
( 10 ,' HBL Lhr', 9 ),
( 11 ,' HBL Gulberg', 10 ),
( 12 ,' ABL Lahr', 8 )
;
CREATE TABLE Trans
([Act_Code] int ,[Charge_Amt] int )
;
INSERT INTO Trans
([Act_Code],[Charge_Amt] ])
VALUES
( 6 , 1000 ),
( 11 , 15000 ),
( 6 , 2000 ),
( 11 , 2500 ),
( 12 , 16000 )
;
使用 Summed as (
SELECT m.Act_Code,Name,Parent_Act,Sum(Charge_Amt)Charge_Amt
FROM Master m
左 外 JOIN Trans t
ON m.Act_Code = t.Act_Code
GROUP BY m.Act_Code,Name,Parent_Act
)
,递归 as (
SELECT Act_Code,Name,Parent_Act,Charge_Amt
FROM 总计
UNION ALL
SELECT s.Act_Code,s.Name,s.Parent_Act,r .Charge_Amt
FROM 递归r
JOIN 求和s
ON r.Parent_Act = s.Act_Code
)
SELECT Act_Code,Name,Parent_Act,Sum(Charge_Amt)Charge_Amt
< span class =code-keyword> FROM 递归
GROUP BY Act_Code ,姓名,Parent_Act
只要证明这个概念,你就必须自己修复缩进。
i have two table one is master act(Act_Code,Name,Parent_Act)
and 2nd is TransactionTable(Act_Code,Amt_Charge).
there are 5 levels of accounts, means hierarchy is upto 5 level. i want sum of all accounts from transaction with appropriate sum of each parent account .
like
Master Table
Act_Code Name Parent_Act
1 | Asset | 1
2 | Cash | 1
3 | A/Rcv | 1
4 | PettyCash | 2
5 | ShopPettyCash | 4
6 | CashforShpVisitor | 5
|
7 | Bank | 1
8 | PakBank | 7
9 | HBL | 8
10 | HBL Lhr | 9
11 HBL Gulberg | 10
12 | ABL Lahr | 8
TransctionTable
Act_Code Charge_Amt
6 | 1000
11 | 15000
6 | 2000
11 | 2500
12 | 16000
Result
Act_Code Name Sum
1 | Asset | 36500
2 | Cash | 3000
3 | A/Rcv | 0
4 | PettyCash | 3000
5 | ShopPettyCash | 3000
6 | CashforShpVisitor | 3000
7 | Bank | 33500
8 | PakBank | 33500
9 | HBL | 17500
10 | HBL Lhr | 17500
11 | HBL Gulberg | 17500
12 | ABL Lahr | 16000
CREATE TABLE Master ([Act_Code] int, [Name] varchar(17), [Parent_Act] int) ; INSERT INTO Master ([Act_Code], [Name], [Parent_Act]) VALUES (1, 'Asset', null), (2, 'Cash', 1), (3, 'A/Rcv', 1), (4, 'PettyCash', 2), (5, 'ShopPettyCash', 4), (6, 'CashforShpVisitor', 5), (7, 'Bank', 1), (8, 'PakBank', 7), (9, 'HBL', 8), (10, 'HBL Lhr', 9), (11, 'HBL Gulberg', 10), (12, 'ABL Lahr', 8) ; CREATE TABLE Trans ([Act_Code] int, [Charge_Amt] int) ; INSERT INTO Trans ([Act_Code], [Charge_Amt]) VALUES (6, 1000), (11, 15000), (6, 2000), (11, 2500), (12, 16000) ; With Summed as ( SELECT m.Act_Code,Name,Parent_Act,Sum(Charge_Amt) Charge_Amt FROM Master m Left Outer JOIN Trans t ON m.Act_Code = t.Act_Code GROUP BY m.Act_Code,Name,Parent_Act ) ,Recursed as ( SELECT Act_Code,Name,Parent_Act,Charge_Amt FROM Summed UNION ALL SELECT s.Act_Code,s.Name,s.Parent_Act,r.Charge_Amt FROM Recursed r JOIN Summed s ON r.Parent_Act = s.Act_Code ) SELECT Act_Code,Name,Parent_Act,Sum(Charge_Amt) Charge_Amt FROM Recursed GROUP BY Act_Code,Name,Parent_Act
Just proving the concept, you'll have to fix the indentation yourself.
这篇关于求和的层次结构查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!