如何从根元素中检索所有子元素。 [英] How to retrive all the child elements from a root element.

问题描述

假设我在表格中添加了一个客户详细信息。



赞

  AgentCode   AgentName  联系 城市  IntroCode   Introname  
 A0001 Mahesh 8130605243德里
 A0002 suresh 9077878787 sonpur A0001 Mahesh kumar pattnayak 
 A0003 rajeev 4353453453 delhi A0001 Mahesh kumar pattnayak 
 A0004 pradeep 992932939 KANPUR A0003 rajeev 
 A0005 VISHAL 92394929394 LUCKNOW A0002 suresh 
 A0006 KARTHIK 92394929394 HYD A0002 suresh 
 A0007 Abhinav 929929394 assam A0001 Mahesh kumar pattnayak 
 A0008 rajesh 3453453453 rajnag A0003 rajeev 
 A0009 Sanjit 923499234 Delhi A0001 Mahesh kumar pattnayak 
 A0010 Dinesh 99399293 delhi A0004 pradeep 

现在我们有代理商代码，姓名，联系方式，城市，简介。代码和介绍。名字。

简介。代码是已添加代理的代理的代码...



现在在c＃中。我想从表中获取子元素。

for ex。如果我输入A0002 - 那么它会显示代理商代码A0002下的所有元素。它就像一个树形结构。但如何获取所有子元素。我不知道该怎么办？

解决方案

它就像MLM（多层次营销）......这是你的查询的解决方案。

Microsoft SQL Server 2008中的递归查询 [ ^ ]

在我正在开发的项目中，我在数据库表中使用了具有树结构的附加列。列命名为：TreePath和TreeLeveel。我正在使用SQL Server，并且在插入或更新时使用数据库触发器自动填充这两列。



TreePath列用于存储元素的路径，TreeLevel用于存储树中元素的深度，在您的情况下可能如下所示：

 AgentCode IntroCode TreeLevel TreePath 
 A0001 0 .A0001。 
 A0002 A0001 1 .A0001.A0002。 
 A0003 A0001 1 .A0001.A0003。 
 A0004 A0003 2 .A0001.A0003.A0004 
依旧... 

在这种情况下你不必使用递归和复杂的查询来检索与孩子的rercord。所以，如果你想获得A0002和所有孩子的记录，你可以使用简单的选择：

  SELECT  *  FROM  YOUR_TABLE_NAME  WHERE  TreePath  LIKE  '  .A0002％' 

我希望它可以帮助你：）

如果您使用的是Microsoft SQL Server 2008或更高版本，则另一个选项是 hierarchyid 类型 [ ^ ]：

分层数据（SQL Server） [ ^ ]

教程：使用hierarchyid数据类型 [ ^ ]

SQL Server中的层次结构ID [ ^ ]

suppose i have added one customer details in a table.

like

AgentCode AgentName Contact	City	IntroCode Introname
A0001	       Mahesh 	8130605243	Delhi		
A0002	       suresh	9077878787	sonpur	A0001	Mahesh kumar pattnayak
A0003	       rajeev	4353453453	delhi	A0001	Mahesh kumar pattnayak
A0004	       pradeep	992932939	KANPUR	A0003	rajeev
A0005	       VISHAL	92394929394	LUCKNOW	A0002	suresh
A0006	       KARTHIK	92394929394	HYD	A0002	suresh
A0007	       Abhinav	929929394	assam	A0001	Mahesh kumar pattnayak
A0008	       rajesh	3453453453	rajnag  A0003	rajeev
A0009	       Sanjit	923499234	Delhi	A0001	Mahesh kumar pattnayak
A0010	       Dinesh	99399293	delhi	A0004	pradeep

now here we have agent code, name, contact, city, intro. code and intro. name.
intro. code is the code of the agent who have added the agent ...

now in c#. i want to acess the child elements from the table.
for ex. if i enter A0002 - then it show all the elements under the Agent Code A0002. It's like a tree structure. but how to acess all the child elements. i don't know what to do ?

解决方案

Its like the MLM(Multi Level Marketing)...Here is a solution for your query.
Recursive Queries in Microsoft SQL Server 2008[^]

Hi,

In project i'm currently developing, i'm using additional columns in database tables which has tree structure. Columns are named: TreePath and TreeLeveel. I'm using SQL Server and both columns are automatically filled using database triggers on insert or update.

TreePath column is used to store path to elements and TreeLevel is used to store depth of element in tree which in your case could look like this:

AgentCode	IntroCode	TreeLevel	TreePath
A0001				    0		.A0001.
A0002		A0001		1		.A0001.A0002.	
A0003		A0001		1		.A0001.A0003.
A0004		A0003		2		.A0001.A0003.A0004
and so on...

In this case you don't have to use recursion and complicated queries to retrieve rercord with childs. So, if you want to get records for A0002 and all childs you can use simple select:

SELECT * FROM YOUR_TABLE_NAME WHERE TreePath LIKE '.A0002%'

I hope it help you :)

Another option, if you're using Microsoft SQL Server 2008 or higher, would be the hierarchyid type[^]:
Hierarchical Data (SQL Server)[^]
Tutorial: Using the hierarchyid Data Type[^]
Hierarchy ID in SQL Server[^]

这篇关于如何从根元素中检索所有子元素。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！