我正在尝试做一个动态查询,通过孩子-父母表,我已经能够通过顶部和第二层的层次查询:
数据:
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)
列Test
仅用于在末尾查看数据是否正确排序
我的代码到目前为止:
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
我怎样才能让这个查询动态地遍历所有的child呢?这是我们想要的输出:
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
以下是我试图实现的视觉解释:
3条答案
按热度按时间xdnvmnnf1#
我看不出动态SQL的必要性,你有层次数据,你想遍历深度优先:在SQL中,这通常是通过递归查询完成。
若要管理行的顺序,可以跟踪每个节点的路径。
考虑:
Demo on DB Fiddle:
如果一个路径可能有100个以上的节点,那么您需要在查询的末尾添加
option(maxrecursion 0)
,否则您将达到SQL Server默认允许的最大递归级别。um6iljoc2#
您可以通过搜索递归查询(如articles或更早的答案)上的材料来找到有关您所问问题的材料。
要创建递归查询,您需要创建一个
CTE
,其中第一个表是锚,就像第一个级别中有列Parent
是NULL
一样。在同一个CTE
中,您不断向该级别添加1。请在Fiddle中找到答案am46iovg3#
似乎相同的答案(我想到的..)张贴的其他同行与伟大的演示。然而,以下示例和this post可能有助于简单和更好地理解递归CTE
DDL公司
递归CTE示例
在您的示例中遵循递归CTE