如何编写Query以获取Hierarchical流 [英] How to write a Query to get the Hierarchical flow

问题描述

对于Accounts表中的每条记录，我有两个表，如Account和Grouptxt，我需要在Grouptxt表中找到依赖项，下面是这两个表的示例数据





帐户表数据： -

I have two tables like Account and Grouptxt for each record in Accounts table i need to find the dependencies in Grouptxt Table,below is the sample data of those 2 tables


Account Table Data:-

#AccountName,ShortName,Type,OpBal,ParentGroup,Move4Report,DebitLimit,CreditLimit
'C' Mannto,'C' Mannto,Customer/Supplier,0.0,Debtors Belgaum,True,0.0,0.0
2M Engineers,2M Engineers,Customer/Supplier,260.0,Debtors Belgaum,True,0.0,0.0
A. A. Engineering,A. A. Engineering,Customer/Supplier,114.0,Debtors Belgaum,False,0.0,0.0
A.C. Enterprises,A.C. Enterprises,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0

Grouptxt表数据： -

#GroupName,ShortName,ParentGroup,isSystem,IsTradingGroup
Asset, Asset, ,True,False
APPLICATION OF FUNDS,APPLICATION OF FUNDS,Asset,False,False
Fixed Assets,Fixed Assets,APPLICATION OF FUNDS,False,False
Gross Block,Gross Block,Fixed Assets,False,False

所以现在我的问题是对于账户表中的每条记录，我需要获得fromo grouptxt表的依赖关系。怎么做？



O / P应该采用这种格式

支出< - 消耗的材料< - 原材料&组件A.C. Enterprises，A.C。企业，客户/供应商，0.0，材料债权人，真实，0.0,0.0



对于记录A.C.Enterprises，依赖关系是原材料&组件 - >消耗的材料 - >支出



这样我需要整张桌子，但没有得到整张桌子怎么做





我这样试过

So now my Question is for each record in Account tables i need to get the dependencies fromo grouptxt table.How to do that?

the O/P Should be in this format
Expenditure <-- Materials Consumed <--Raw Material & Components A.C. Enterprises,A.C. Enterprises,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0

For the Record A.C.Enterprises the Dependencies are Raw Material & Components --> Materials Consumed --> Expenditure

like this i need to get for entire table,but not getting how to do that for the entire table


I Tried like this

SELECT #AccountName
      ,a.ShortName
      ,a.Type
      ,a.OpBal
      ,a.ParentGroup
      ,a.Move4Report
      ,a.DebitLimit
      ,a.CreditLimit
      ,a.row_id,
      g.ParentGroup AS GrpTxt_ParentGroup
FROM Grouptxt   g inner join account a
ON a.ParentGroup=g.ShortName
WHERE #AccountName='Increase/Decrease In Finished Goods'

SELECT * FROM Grouptxt where ParentGroup='Raw Material & Components'

SELECT * FROM Grouptxt Where ShortName='Raw Material & Components'

SELECT * FROM Grouptxt Where ShortName='Materials Consumed'

SELECT * FROM Grouptxt Where ShortName='Expenditure'

先谢谢

Thanks in Advance

推荐答案

我不知道确切的答案，但它可能会对你有所帮助：

SQL中的分层和递归查询 [ ^ ]

使用CTE（公用表表达式）和深度优先策略的分层数据 - SQL Server [ ^ ]

在SQL中管理分层数据 [ ^ ]

使用hierarchyid数据类型（数据库引擎） [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

I don't know the exact answer, but it might help you:
Hierarchical and recursive queries in SQL[^]
Hierarchical data using CTE (Common Table Expression) with Depth First Strategy - SQL Server[^]
Managing Hierarchical Data in SQL[^]
Using hierarchyid Data Types (Database Engine)[^]
Recursive Queries Using Common Table Expressions[^]

这篇关于如何编写Query以获取Hierarchical流的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！