本文介绍了求和的层次结构查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我有两个表一个是主法案(Act_Code,Name,Parent_Act) 和第二个是TransactionTable(Act_Code,Amt_Charge)。 有5个级别的帐户,意味着层次结构最多为5级。我需要交易中所有账户的总和以及每个父账户的适当金额。 喜欢 主表 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 ),( 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 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 .likeMaster TableAct_Code Name Parent_Act1 | Asset | 12 | Cash | 13 | A/Rcv | 14 | PettyCash | 25 | ShopPettyCash | 46 | CashforShpVisitor | 5 |7 | Bank | 18 | PakBank | 79 | HBL | 810 | HBL Lhr | 911 HBL Gulberg | 1012 | ABL Lahr | 8TransctionTableAct_Code Charge_Amt6 | 100011 | 150006 | 200011 | 250012 | 16000ResultAct_Code Name Sum1 | Asset | 365002 | Cash | 30003 | A/Rcv | 04 | PettyCash | 30005 | ShopPettyCash | 30006 | CashforShpVisitor | 30007 | Bank | 335008 | PakBank | 335009 | HBL | 1750010 | HBL Lhr | 1750011 | HBL Gulberg | 1750012 | 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_AmtFROM RecursedGROUP BY Act_Code,Name,Parent_ActJust proving the concept, you'll have to fix the indentation yourself. 这篇关于求和的层次结构查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
09-02 13:50