sql-server - 分层数据的动态 T-SQL 查询

Question

我正在尝试进行动态查询以遍历子表 - 父表，并且我已经能够遍历顶级和二级分层查询:

数据:

create table temp 
(
     Pos int
    ,Child nvarchar(18)
    ,Parent nvarchar(18)
    ,Test int
);

insert into temp (Pos, Child, Parent, Test)
values
(1, 'A', NULL, 1),
(2, 'J', NULL, 10),
(3, 'P', NULL, 16),
(4, 'Y', NULL, 25),
(1, 'B', 'A', 2),
(2, 'E', 'A', 5),
(1, 'C', 'B', 3),
(2, 'D', 'B', 4),
(1, 'F', 'E', 6),
(2, 'G', 'E', 7),
(1, 'H', 'G', 8),
(2, 'I', 'G', 9),
(1, 'K', 'J', 11),
(2, 'L', 'J', 12),
(3, 'M', 'J', 13),
(1, 'N', 'M', 14),
(2, 'O', 'M', 15),
(5, 'Z', NULL, 26),
(1, 'Q', 'P', 17),
(2, 'S', 'P', 19),
(3, 'T', 'P', 20),
(4, 'X', 'P', 24),
(1, 'R', 'Q', 18),
(1, 'U', 'T', 21),
(2, 'V', 'T', 22),
(3, 'W', 'T', 23)

列测试只是在末尾查看数据是否正确排序

到目前为止我的代码:

declare @sql nvarchar(max);
declare @tlp nvarchar(max); --top level parents
declare @i nvarchar(4);
declare @j nvarchar(4);
declare @l nvarchar(4); --level

set @tlp = ';with tlp as (
       select ROW_NUMBER() over (order by Pos) as j, * from temp where Parent IS NULL
       )';
set @i = 1;
set @j = (select COUNT(*) as j from temp where Parent IS NULL);
set @sql = @tlp;

while @i < @j
    begin
        set @l = 1;
        set @sql += '
            select ' + @l + ' as Level, * from tlp where j = ' + @i
        
        set @l = @l + 1
        set @sql += '
            union all
            select ' + @l + ' as Level, ROW_NUMBER() over (order by Pos), * from temp where Parent = (select Child from tlp where j = ' + @i + ')'
        set @i = @i + 1
        if @i < @j set @sql += '
            union all'
end;

exec(@sql);

输出:

level   j   Pos Child   Parent  Test
1       1   1   A       NULL    1
2       1   1   B       A       2
2       2   2   E       A       5
1       2   2   J       NULL    10
2       1   1   K       J       11
2       2   2   L       J       12
2       3   3   M       J       13
1       3   3   P       NULL    16
2       1   1   Q       P       17
2       2   2   S       P       19
2       3   3   T       P       20
2       4   4   X       P       24
1       4   4   Y       NULL    25

我如何扩展查询以动态遍历所有子项？这是所需的输出:

Level   j   Pos Child   Parent  Test
1       1   1   A       NULL    1
2       1   1   B       A       2
3       1   1   C       B       3
3       2   2   D       B       4
2       2   2   E       A       5
3       1   1   F       E       6
3       2   2   G       E       7
4       1   1   H       G       8
4       2   2   I       G       9
1       2   2   J       NULL    10
2       1   1   K       J       11
2       2   2   L       J       12
2       3   3   M       J       13
3       1   1   N       M       14
3       2   2   O       M       15
1       3   3   P       NULL    16
2       1   1   Q       P       17
3       1   1   R       Q       18
2       2   2   S       P       19
2       3   3   T       P       20
3       1   1   U       T       21
3       2   2   V       T       22
3       3   3   W       T       23
3       4   4   X       P       24
1       4   4   Y       NULL    25
1       5   5   Z       NULL    26

这是我试图实现的视觉解释:

Answer 1

我根本看不到动态 SQL 的必要性。您有想要深度优先遍历的分层数据:在 SQL 中，这通常通过递归查询完成。

要管理行的顺序，您可以跟踪每个节点的路径。

考虑:

with cte as (
    select t.*, 1 lvl, cast(child as nvarchar(max)) path 
    from temp t 
    where parent is null
    union all
    select t.*, c.lvl + 1, c.path + '/' + cast(t.child as nvarchar(max))
    from cte c
    inner join temp t on t.parent = c.child
)
select * from cte order by path

Demo on DB Fiddle :

Pos | Child | Parent | Test | lvl | path   --: | :---- | :----- | ---: | --: | :------  1 | A     | null   |    1 |   1 | A        1 | B     | A      |    2 |   2 | A/B      1 | C     | B      |    3 |   3 | A/B/C    2 | D     | B      |    4 |   3 | A/B/D    2 | E     | A      |    5 |   2 | A/E      1 | F     | E      |    6 |   3 | A/E/F    2 | G     | E      |    7 |   3 | A/E/G    1 | H     | G      |    8 |   4 | A/E/G/H  2 | I     | G      |    9 |   4 | A/E/G/I  2 | J     | null   |   10 |   1 | J        1 | K     | J      |   11 |   2 | J/K      2 | L     | J      |   12 |   2 | J/L      3 | M     | J      |   13 |   2 | J/M      1 | N     | M      |   14 |   3 | J/M/N    2 | O     | M      |   15 |   3 | J/M/O    3 | P     | null   |   16 |   1 | P        1 | Q     | P      |   17 |   2 | P/Q      1 | R     | Q      |   18 |   3 | P/Q/R    2 | S     | P      |   19 |   2 | P/S      3 | T     | P      |   20 |   2 | P/T      1 | U     | T      |   21 |   3 | P/T/U    2 | V     | T      |   22 |   3 | P/T/V    3 | W     | T      |   23 |   3 | P/T/W    4 | X     | P      |   24 |   2 | P/X      4 | Y     | null   |   25 |   1 | Y        5 | Z     | null   |   26 |   1 | Z      

如果一个路径可能有超过100个节点，那么你需要在查询的末尾添加option(maxrecursion 0)，否则你将达到SQL Server允许的最大递归级别默认情况下。