从父子表生成字符串树分支

我有一个父子表，如下所示：

child   |   father
H       :   G
F       :   G
G       :   D
E       :   D
A       :   E
B       :   C
C       :   E

我希望 sql server 生成类似的东西（正如这个问题中所问的那样）将一系列父子关系转化为层次树？ https://stackoverflow.com/questions/2915748/how-can-i-convert-a-series-of-parent-child-relationships-into-a-hierarchical-tre但在 sql 中而不是在 php 中）：

 D
 ├── E
 │   ├── C
 │   │   └── B
 │   └── A   
 └── G
     ├── F
     └── H

当然，结果可以是一个字符串列，我可以在文本编辑器中复制它。

我还希望有第二个查询来生成如下内容：

 father |   descendants
 D      |   D -> E -> C -> B
 D      |   D -> E -> A
 D      |   D -> G -> F
 D      |   D -> G -> H

在前一种情况下，只有一棵树有一个父亲，但在表中可能有更多的树有多个父亲，就像如果 D 不存在时这棵树一样。

如果请求的第一部分（伪视觉树）无法完成，也可以。重要的部分是桌子。

我已经尝试做这样的事情很长时间了，但我无法达到想要的结果。

TNX

这很有趣。不过，使用 SQL 而不是其他语言可能效率较低。想起来还是很有趣的。

我是这样做的。

初始化表：

SET NOCOUNT ON
DECLARE @Table TABLE ([Child] NVARCHAR(10), [Parent] NVARCHAR(10))
INSERT @Table VALUES ('H','G'),('F','G'),('G','D'),('E','D')
,('A','E'),('B','C'),('C','E'),('D', NULL),('Z','E'),('X','Z'),('Y','Z')
,('L',NULL),('M','L'),('N','L'),('P','N'),('Q','L'), ('R',NULL),('S', 'R')
IF OBJECT_ID('tempdb..#tmptable') IS NOT NULL DROP TABLE #tmptable
; WITH T AS (
    SELECT Parent, Child, 1 [Level]
    FROM @Table
    WHERE Parent IS NULL
    UNION ALL
    SELECT a.Parent, a.Child, T.[Level] + 1
    FROM @Table a
    JOIN T ON a.Parent = T.Child)
SELECT *
INTO #tmptable
FROM T

对于查询 1，我使用动态 SQL，假设您不知道任何给定父级可以拥有的最大后代数量：

DECLARE @SQL NVARCHAR(MAX)
DECLARE @a INT = (SELECT MAX(Level) FROM #tmptable)
DECLARE @b INT = 2
SET @SQL = 
'; WITH CTE AS (
    SELECT T1.Child Father'
WHILE @b<= @a BEGIN
    SET @SQL += '
        , ISNULL(T' + CONVERT(NVARCHAR, @b) + '.Child, '''') Child' + CONVERT(NVARCHAR, @b - 1)
    SET @b += 1
END
SET @SQL +='
        , ROW_NUMBER() OVER (ORDER BY T1.Child'
SET @b =  2 
WHILE @b <= @a BEGIN        
    SET @SQL += ', T' + CONVERT(NVARCHAR, @b) + '.Child'
    SET @b += 1
END
SET @SQL += ') RN
    FROM #tmptable T1'
SET @b = 2
WHILE @b <= @a BEGIN
    SET @SQL += '
    LEFT JOIN #tmptable T' + CONVERT(NVARCHAR, @b) + ' ON T' + CONVERT(NVARCHAR, @b) +'.Parent = T' + CONVERT(NVARCHAR, @b - 1) + '.Child'
    SET @b += 1
END
SET @SQL += '
    WHERE T1.Parent IS NULL
    GROUP BY T1.Child'
SET @b = 2
WHILE @b <= @a BEGIN
    SET @SQL += ', T' + CONVERT(NVARCHAR, @b) + '.Child'
    SET @b += 1
END
SET @SQL += ')
SELECT ''<ul>'' + REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), (
    SELECT CASE WHEN RN = 1 THEN ''<li>''
            WHEN (SELECT Father FROM CTE WHERE RN = C.RN - 1) <> Father THEN ''<li>''
            ELSE '''' END --Fatherli
        , CASE WHEN RN = 1 THEN Father
            WHEN (SELECT Father FROM CTE WHERE RN = C.RN - 1) <> Father THEN Father
            ELSE '''' END --Father
        , CASE WHEN RN = 1 THEN ''</li>''
            WHEN (SELECT Father FROM CTE WHERE RN = C.RN - 1) <> Father THEN ''</li>''
            ELSE '''' END --Fathercli
        , CASE WHEN RN = 1 AND Child1 <> '''' THEN ''<ul>''
            WHEN (SELECT Father FROM CTE WHERE RN = C.RN - 1) <> Father AND Child1 <> '''' THEN ''<ul>''
            ELSE '''' END --Fatherul'
SET @b = 2
WHILE @b <= @a BEGIN
    SET @SQL += '
        , CASE WHEN RN = 1 AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' THEN ''<li>''
            WHEN (SELECT Child' + CONVERT(NVARCHAR, @b-1) + ' FROM CTE WHERE RN = C.RN - 1) <> Child' + CONVERT(NVARCHAR, @b-1) + ' AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' THEN ''<li>''
            ELSE '''' END --Child' + CONVERT(NVARCHAR, @b-1) + 'li
        , CASE WHEN RN = 1 AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' THEN Child' + CONVERT(NVARCHAR, @b-1) + '
            WHEN (SELECT Child' + CONVERT(NVARCHAR, @b-1) + ' FROM CTE WHERE RN = C.RN - 1) <> Child' + CONVERT(NVARCHAR, @b-1) + ' AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' THEN Child' + CONVERT(NVARCHAR, @b-1) + '
            ELSE '''' END --Child' + CONVERT(NVARCHAR, @b-1) + '
        , CASE WHEN RN = 1 AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' THEN ''</li>''
            WHEN (SELECT Child' + CONVERT(NVARCHAR, @b-1) + ' FROM CTE WHERE RN = C.RN - 1) <> Child' + CONVERT(NVARCHAR, @b-1) + ' AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' THEN ''</li>''
            ELSE '''' END --Child' + CONVERT(NVARCHAR, @b-1) + 'cli'
    IF @a <> @b 
        SET @SQL += '
        , CASE WHEN RN = 1 AND Child' + CONVERT(NVARCHAR, @b-1) + ' <> '''' AND Child' + CONVERT(NVARCHAR, @b) + ' <> '''' THEN ''<ul>''
            WHEN (SELECT Child' + CONVERT(NVARCHAR, @b-1) + ' FROM CTE WHERE RN = C.RN - 1) <> Child' + CONVERT(NVARCHAR, @b-1) + ' AND Child' + CONVERT(NVARCHAR, @b) + ' <> '''' THEN ''<ul>''
            ELSE '''' END --Child' + CONVERT(NVARCHAR, @b-1) + 'ul'
    SET @b += 1
END
SET @b -= 3
WHILE @b > 0 BEGIN
    SET @SQL += '
        , CASE WHEN RN = (SELECT MAX(RN) FROM CTE) AND Child' + CONVERT(NVARCHAR, @b+1) + ' <> '''' THEN ''</ul>''
            WHEN (SELECT Child' + CONVERT(NVARCHAR, @b) + ' FROM CTE WHERE RN = C.RN + 1) <> Child' + CONVERT(NVARCHAR, @b) + ' AND Child' + CONVERT(NVARCHAR, @b+1) + ' <> '''' THEN ''</ul>''
            ELSE '''' END --Child' + CONVERT(NVARCHAR, @b) + 'cul'
    SET @b -= 1
END
SET @SQL += '
        , CASE WHEN RN = (SELECT MAX(RN) FROM CTE) AND Child1 <> '''' THEN ''</ul>''
            WHEN (SELECT Father FROM CTE WHERE RN = C.RN + 1) <> Father AND Child1 <> '''' THEN ''</ul>''
            ELSE '''' END --Fathercul
    FROM CTE C
    FOR XML PATH (''''))), ''&lt;'', ''<''), ''&gt;'', ''>'') + ''</ul>'''
EXEC(@SQL)
-- PRINT @SQL

输出（对于我输入的值）是<ul><li>D</li><ul><li>E</li><ul><li>A</li><li>C</li><ul><li>B</li></ul><li>Z</li><ul><li>X</li><li>Y</li></ul></ul><li>G</li><ul><li>F</li><li>H</li></ul></ul><li>L</li><ul><li>M</li><li>N</li><ul><li>P</li></ul><li>Q</li></ul><li>R</li><ul><li>S</li></ul></ul>显示如下：

• D
• E
• A
• C
• B
• Z
• X
• Y
• G
• F
• H
• L
• M
• N
• P
• Q
• R
• S

对于第二个查询，可能有更简单的方法来完成它，但我想为什么不使用更动态的 SQL？

DECLARE @i INT = (SELECT MAX([Level]) FROM #tmptable), @j INT = 2
DECLARE @SQL2 NVARCHAR(MAX)
SET @SQL2 = 'SELECT T1.Child Father, T1.Child '
WHILE @j <= @i BEGIN
    SET @SQL2 += '+ ISNULL('' -> '' + T' + CONVERT(NVARCHAR, @j) + '.Child, '''')'
    SET @j += 1
END
SET @j = 2
SET @SQL2 += ' Descendants FROM #tmptable T1'
WHILE @j <= @i BEGIN
    SET @SQL2 += ' LEFT JOIN #tmptable T' + CONVERT(NVARCHAR, @j) + ' ON T' + CONVERT(NVARCHAR, @j) + '.[Parent] = T' + CONVERT(NVARCHAR, @j-1) + '.[Child]'
    SET @j += 1
END
SET @j = 2
SET @SQL2 += ' WHERE T1.[Parent] IS NULL ORDER BY T1.[Child]'
WHILE @j <= @i BEGIN
    SET @SQL2 += ', T' + CONVERT(NVARCHAR, @j) + '.[Child]'
    SET @j += 1
END
EXEC(@SQL2)