创建树视图循环查询 [英] create treeview looping query

问题描述

我设计了一个树状结构如下:*parentID = 0 表示树根

i have designed a treeview structure as below: *parentID = 0 means tree root

ID    parentID
1        0 
2        0  
3        1
4        1
5        1
6        3
7        4
8        5
9        5
10       5
11       2
12       2
13      10

当我传递参数1"时，它将返回根1"下所有内容的结果，预期结果:

When i pass the parameter of "1" it will return the result of everything under Root "1", Expected result:

ID  parentID
1        0
3        1
4        1
5        1
6        3
7        4
8        5
9        5
10       5
13      10

可以通过一个查询来解决这个问题吗?

possible to solve this with a single query?

推荐答案

按照您现在存储树的方式，不可能在一个纯 SQL 查询中获取整个树.您需要使用其他语言(C#、支持循环的存储过程等)编写一个循环来获取树.

In the way you stored the tree now, it is not possible to get the entire tree in one pure SQL query. You need to write a loop in some other language (C#, stored procedure that supports loops, ...) to fetch the tree.

这篇是一篇很好的文章，描述了如何使用您存储数据的方式树(包括获取树和从树中删除项目).

This is a nice article describing how to work with your way of storing the tree (including fetching the tree and removing items from the tree).

更有趣的是，该文章还描述了一种将树存储在数据库表中的方法，确实允许在单个查询中获取整个树.这称为预序树遍历.您可以查找它以获取更多信息.我找到了一个 C# 实现.它比您现在使用的方式涉及更多的逻辑，但对除最小树之外的所有树都具有更高的性能.

Even more interesting, that article also describes a way to store trees in a database table that does allow fetching the entire tree in a single query. It's called pre-order tree traversal. You can look it up for more information. I've found a C# implementation. It involves some more logic than the way you are using now, but is more performant for all but the smallest trees.

这篇关于创建树视图循环查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！