来自递归表的Treeview（C＃） [英] Treeview (C#) from recursive table

问题描述

我希望获得与prod代码'500'相关联的所有comp代码

该表将包含该产品的1个产品和comp代码。 comp代码本身将在表中具有prod代码，并且该prod代码可能具有与表中相关联的comp代码。那就是骚乱。我正在看一个CTE，它可以通过表格来计算水平/子级别以及产品和组件。



我尝试了什么：



如何创建树视图以反映节点级别，以及如何使用sql CTE执行此操作？



任何想法都会很棒谢谢

I am looking to get all the comp codes associated with prod code '500'
The table will have 1 product and comp codes for that product. The comp codes themselves will have a prod code in the table, and that prod code may well have comp codes associated with it in the table. That is the hierrachy. I am looking at a CTE that can go through the table and work out the levels/sublevels and products and components.

What I have tried:

How can I create the treeview to reflect the node levels, and how to do this with a sql CTE?

Any idea would be great thanks

推荐答案

请先阅读我对这个问题的评论。



嗯，MSDN文档中有很多例子：

如何：将TreeView绑定到具有不确定深度的数据 [ ^ ]

演练：在TreeView控件中显示分层数据 [ ^ ]

如何：使用TreeView显示分层数据 [ ^ ]



请按照链接查看如何从TreeView插入节点使用C＃代码的分层数据。



对于CTE ...您可以使用CTE（递归查询）来确定节点深度的级别，但是您的数据必须得到纠正。请在下面学习示例：

Please, read my comment to the question first.

Well, MSDN documentation is full of examples:
How to: Bind a TreeView to Data That Has an Indeterminable Depth[^]
Walkthrough: Displaying Hierarchical Data in a TreeView Control[^]
How to: Use a TreeView to Display Hierarchical Data[^]

Please, follow the links to find out how to insert nodes to TreeView from hierarchical data using C# code.

As to the CTE... You can use CTE (recursive query) to determine the level of node depth, but your data have to be corrected. Please, study below example:

DECLARE @tmp TABLE([prod code] NVARCHAR(255), [comp code] NVARCHAR(255))

INSERT INTO @tmp ([prod code], [comp code])
VALUES('Root', NULL),
('Root', '21223'),
('21223', 'part1'),
('21223', 'part2'),
('part2', 'subpart'),
('part2', 'subpart2'),
('subpart', 'lowerlevel')


;WITH CTE AS
(
	SELECT 1 AS NodeLevel, [prod code] AS NodeName
	FROM @tmp
	WHERE [comp code] IS NULL 
	UNION ALL
	SELECT t1.NodeLevel + 1 AS NodeLevel, t2.[comp code] AS NodeName
	FROM CTE AS t1 INNER JOIN @tmp AS t2 ON t1.NodeName = t2.[prod code] 
	WHERE t2.[comp code] IS NOT NULL

)
SELECT *
FROM CTE 

结果：

Result:

NodeLevel	NodeName
1			Root
2			21223
3			part1
3			part2
4			subpart
4			subpart2
5			lowerlevel

有关CTE的更多信息，请参阅：

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

SQL SERVER - 使用递归CTE介绍分层查询 - A Primer - 与Pinal Dave一起访问SQL权限 [ ^ ]

CTE递归查询数据层次结构（父子层次结构） [ ^ ]

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



试试！

For further information about CTE, please see:
Recursive Queries Using Common Table Expressions[^]
SQL SERVER - Introduction to Hierarchical Query using a Recursive CTE - A Primer - Journey to SQL Authority with Pinal Dave[^]
CTE Recursive query for data hierarchy(Parent Child hierarchy)[^]
Depth First Strategy with Hierarchical data using CTE (Common Table Expression) - SQL Server | The SQL Ideas[^]

Try!

您要搜索的技术形容词是分层的。

这是一个基本的搜索，提供足够的链接以获取线索：

C＃层次树视图 [ ^ ]



祝你好运，并保持良好的工作：）

The technical adjective for what you are searching for is "hierarchical".
Here's a basic search providing enough links to have clues about it:
C# hierarchical treeview[^]

Good luck, and keep up the good work :)

最后，我做了一个递归函数C＃创建树视图。我无法在SQL中找到实现此目的的方法。我想出的过程是创建一个保持列表，以及一个具有List< string>，Level和TreeNode对象的类。通过知道表中存在的组件作为产品，我将创建另一个节点并从列表中删除该产品。



所以实际上，我最后得到的列表包含一个带有自己列表的对象。对于900多个产品树，处理需要10秒钟。也许有一种更简洁的方式，但这对我有用。

In the end, I done a recursive function in C# to create the treeview. I could not find a way to achieve this in SQL. The process I came up with was to create a holding list, and a class that had a List<string>, Level, and TreeNode object. By knowing that a component existed in the table as a product, I would create another node and remove the product from the list.

So in effect, I ended up with a list which contains an object with its own list. For a 900+ product tree, the processing took 10 seconds. Maybe there is a neater way but that will do for me.

这篇关于来自递归表的Treeview（C＃）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！