如何遍历“父"表的 SQL 表和孩子"行在同一个表中 [英] How to iterate through an SQL table which "parent" and child" rows are in same table

问题描述

在一个表中有列 ID、Title 和 ParentID.ParentID 用于同一表中被视为其父项的条目的 ID - 因此，ParentID 为 NULL 的任何条目本身就是父项.

In a table there are the columns ID, Title and ParentID. ParentID is used for the ID of the entry in the same table which is considered its parent - thus any entry in which ParentID is NULL is one which is itself a parent.

我需要一个查询，它会遍历每个父级并列出它下面的所有子级(最好用连字符或其他东西来表示其从属关系)有谁知道如何做到这一点或如何为我指明正确的方向?

I need a query which will iterate through each parent and list any child below it (ideally with a hyphen or something to denote its subordination) does anyone know how this can be done or how to point me in the right direction?

(我研究了 T-SQL 并阅读了许多类似的在线问题，但是我的 SQL 不够清晰，无法理解它，所以我非常感谢一些指针！)

(I've looked into T-SQL and read many similar online questions however my SQL isn't quite sharp enough to make sense of it, so I'd greatly appreciate some pointers!)

推荐答案

给你！

WITH n(ID, Title) AS 
                    (SELECT ID, Title
                    FROM YourTable
                    WHERE ID = (SELECT TOP 1 ID FROM YourTable WHERE ParentID IS NULL)
                    UNION ALL 
                    SELECT nplus1.ID, nplus1.Title
                    FROM YourTable as nplus1, n 
                    WHERE n.ID = nplus1.ParentID) 
                    SELECT ID, Title FROM n

这篇关于如何遍历“父"表的 SQL 表和孩子"行在同一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！