从父子表生成字符串树枝 [英] Generating string tree branches from father-children table

问题描述

我有一个如下的父子表：

I have a father-children table like the following:

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

我希望sql server生成类似这样的内容（因为在此问题中被问到转换一系列父项子关系变成层次树？，但在tsql中而不在php中）：

I'd like sql server to generate something like this (as it was asked in this question Convert a series of parent-child relationships into a hierarchical tree? but in tsql and not in php):

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

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

Of course the result can be a string column that i can copy in an text editor.

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

I'd like also to have a second query that generate something like this:

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

在前一种情况下，只有一棵树有一个单亲父亲，但在桌子上可能有

In the previous case there is just one tree with a single father but in the table there could be more the one with multiple fathers, like this tree would be if D would not exist.

如果无法使用requast的第一部分（伪可视化树），则该树将具有多个父亲。很好。重要的部分是表格。

If the first part of the requast (the pseudo visual tree) is not possible to do it's fine. The important part is the table.

长期以来，我一直试图做这样的事情，但我无法获得想要的结果。

I've tried to do something like this for long bu i could not attain the wanted results.

TNX

推荐答案

这很有趣。不过，用SQL而不是其他某种语言来执行此操作可能效率低下。仍然很有趣。

This is interesting. It's probably inefficient to do it in SQL rather than some other language, though. Still fun to think about.

这是我的操作方式。

初始化表格：

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给定父母可以拥有的后代数量：

For Query 1, I'm using dynamic SQL under the assumption you don't know the maximum amount of descendants any given parent could have:

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 D lt / li ul E lt / li ul ul A C Ci / Li Ui Li Bi / Li Ci / ul Ui Li Zi / li Ui Li Xi X Lii Y ; / li< / ul> / ul< li> G< li< ul< li" F< / li" li" H< / li< / ul> < / ul> li< / li> ul< li> M< li< li> N< / li< ul> li< P< / li> ； / ul＞ li Q ／ li＞ / ul＞ li R ／ li ul ＜li li S ／ li ＜/ ul＞ / ul； 显示如下：

The output (for the values I input) is <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> which displays as such:

• D
• E
• A
• C
• B
• X
• Y
• F
• H
• M
• N
• P
• S

对于第二个查询，可能有更简单的方法来实现，但是我想出了为什么不使用更多动态SQL？

For the second query, there are probably easier ways to do it, but I figured why not go with more dynamic 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)

这篇关于从父子表生成字符串树枝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！