一般来说,它们的结构类似于以下内容：

WITH cte_name ( column_name [,...n] )
AS (
    –- Anchor
    CTE_query_definition

    UNION ALL

    –- Recursive portion
    CTE_query_definition
)
-- Statement using the CTE
SELECT * FROM cte_name

执行此操作时,sql Server将执行类似于以下操作(从MSDN转换为更简单的语言)：

>将CTE表达式拆分为锚点和递归成员.
>运行锚点,创建第一个结果集.
>运行递归部分,前一步作为输入.
>重复步骤3,直到返回一个空集合.
>返回结果集.这是一个UNION ALL的所有锚和所有的递归步骤.

对于这个具体的例子,请尝试这样：

With hierarchy (id,[location id],name,depth)
As (
    -- selects the "root" level items.
    Select ID,[LocationID],Name,1 As depth
    From dbo.Locations
    Where ID = [LocationID]

    Union All

    -- selects the descendant items.
    Select child.id,child.[LocationID],child.name,parent.depth + 1 As depth
    From dbo.Locations As child
    Inner Join hierarchy As parent
        On child.[LocationID] = parent.ID
    Where child.ID != parent.[Location ID])
-- invokes the above expression.
Select *
From hierarchy

给出你的例子数据,你应该得到这样的：

ID     | Location ID | Name  | Depth
_______| __________  |______ | _____
1331   | 1331        | House |     1
1321   | 1331        | Room  |     2
2141   | 1321        | Bed   |     3

请注意,“健身房”不包括在内.根据您的示例数据,它的ID与其[位置ID]不匹配,因此它不会是根级别项.它的位置ID,2231,不会出现在有效父ID的列表中.

编辑1：

您已经询问将其转化为C#数据结构.在C#中表示层次结构有许多不同的方法.这是一个简单的例子.一个真正的代码样本无疑会更广泛.

第一步是定义层次结构中的每个节点.除了包含节点中每个基准的属性之外,我还包括“父子”和“子”属性,以及添加子节点和获取子节点的方法. Get方法将搜索节点的整个后代轴,而不仅仅是节点自己的子节点.

public class LocationNode {
    public LocationNode Parent { get; set; }
    public List<LocationNode> Children = new List<LocationNode>();

    public int ID { get; set; }
    public int LocationID { get; set; }
    public string Name { get; set; }

    public void Add(LocationNode child) {
        child.Parent = this;
        this.Children.Add(child);
    }

    public LocationNode Get(int id) {
        LocationNode result;
        foreach (LocationNode child in this.Children) {
            if (child.ID == id) {
                return child;
            }
            result = child.Get(id);
            if (result != null) {
                return result;
            }
        }
        return null;
    }
}

现在你要填充你的树.你在这里有一个问题：很难以错误的顺序填充树.在添加子节点之前,您真的需要对父节点的引用.如果您不得不按顺序执行,您可以通过进行两次通过(一个创建所有节点,另一个创建树)来缓解问题.但是,在这种情况下,这是不必要的.

如果您使用上面提供的SQL查询并通过深度列进行排序,则可以在数学上确定在遇到父节点之前永远不会遇到子节点.因此,您可以一次性完成此操作.

您仍然需要一个节点作为您的树的“根”.你可以决定这个是“House”(从你的例子),还是你为这个目的创建一个虚构的占位符节点.我建议稍后.

所以,给代码！同样,这是为了简化和可读性而优化的.您可能希望在生产代码中处理一些性能问题(例如,不需要不断查找“父”节点).我已经避免了这些优化,因为它们增加了复杂性.

// Create the root of the tree.
LocationNode root = new LocationNode();

using (sqlCommand cmd = new sqlCommand()) {
    cmd.Connection = conn; // your connection object,not shown here.
    cmd.CommandText = "The above query,ordered by [Depth] ascending";
    cmd.CommandType = CommandType.Text;
    using (sqlDataReader rs = cmd.ExecuteReader()) {
        while (rs.Read()) {
            int id = rs.GetInt32(0); // ID column
            var parent = root.Get(id) ?? root;
            parent.Add(new LocationNode {
                ID = id,LocationID = rs.GetInt32(1),Name = rs.GetString(2)
            });
        }
    }
}

当当！根位置节点现在包含整个层次结构.顺便说一下,我没有实际执行这个代码,所以请让我知道,如果你发现任何明显的问题.

编辑2

要修复示例代码,请进行以下更改：

删除此行：

// Create an instance of the tree
TreeView t1 = new TreeView();

这行实际上并不是一个问题,但应该被删除.你的意见不准确你并没有真正把一棵树分配给控件.相反,您正在创建一个新的TreeView,将其分配给t1,然后立即将不同的对象分配给t1.您创建的TreeView将在下一行执行时立即丢失.

修复你的sql语句

// sql Commands
string getLocations = "SELECT ID,Name FROM dbo.Locations";

使用ORDER BY子句将此sql语句替换为我之前提出的sql语句.阅读我以前的编辑,解释为什么“深度”很重要：你真的想要以特定的顺序添加节点.您无法添加子节点,直到有父节点.

可选地,我认为您不需要sqlDataAdapter和DataTable的开销.我最初建议的DataReader解决方案比较简单,易于使用,在资源方面更有效率.

此外,大多数C#sql对象都实现了IDisposable,因此您需要确保正确使用它们.如果某些东西实现了IDisposable,请确保使用语句包装它(请参阅我以前的C#代码示例).

修复树形循环

你只会得到父节点和子节点,因为你有一个父节点的循环和子节点的内循环.你必须已经知道,你没有得到孙子,因为你没有添加代码.

你可以添加一个内在的循环来获得孙子,但显然你要求帮助,因为你已经意识到这样做只会导致疯狂.如果你想要孙子孙会怎么样？内内内循环？这种技术是不可行的.

你可能以为在这里递归.这是一个完美的地方,如果你正在处理树状结构,它最终会出现.现在您已经编辑了您的问题,很明显,您的问题与sql有关.你真正的问题是递归.有人可能最终会出来,为此设计递归解决方案.这将是一个完全有效的,也许是可取的方法.

但是,我的答案已经覆盖了递归部分 – 它已经将其移动到sql层.因此,我会保留我以前的代码,因为我觉得这是一个合适的通用答案的问题.对于您的具体情况,您需要进行一些修改.

首先,你不需要我建议的LocationNode类.你正在使用TreeNode,这样可以正常工作.

其次,TreeView.FindNode类似于我建议的LocationNode.Get方法,除了FindNode要求节点的完整路径.要使用FindNode,您必须修改sql以提供此信息.

因此,您的整个PopulateTree函数应该如下所示：

public void PopulateTree(TreeView t1) {

    // Clear any exisiting nodes
    t1.Nodes.Clear();

    using (sqlConnection connection = new sqlConnection()) {
        connection.ConnectionString = "((replace this string))";
        connection.Open();

        string getLocations = @"
            With hierarchy (id,depth,[path])
            As (

                Select ID,1 As depth,Cast(Null as varChar(max)) As [path]
                From dbo.Locations
                Where ID = [LocationID]

                Union All

                Select child.id,parent.depth + 1 As depth,IsNull(
                        parent.[path] + '/' + Cast(parent.id As varChar(max)),Cast(parent.id As varChar(max))
                    ) As [path]
                From dbo.Locations As child
                Inner Join hierarchy As parent
                    On child.[LocationID] = parent.ID
                Where child.ID != parent.[Location ID])

            Select *
            From hierarchy
            Order By [depth] Asc";

        using (sqlCommand cmd = new sqlCommand(getLocations,connection)) {
            cmd.CommandType = CommandType.Text;
            using (sqlDataReader rs = cmd.ExecuteReader()) {
                while (rs.Read()) {
                    // I guess you actually have GUIDs here,huh?
                    int id = rs.GetInt32(0);
                    int locationID = rs.GetInt32(1);
                    TreeNode node = new TreeNode();
                    node.Text = rs.GetString(2);
                    node.Value = id.ToString();

                    if (id == locationID) {
                        t1.Nodes.Add(node);
                    } else {
                        t1.FindNode(rs.GetString(4)).ChildNodes.Add(node);
                    }
                }
            }
        }
    }
}

如果您发现任何其他错误,请通知我！