T/SQL 中的递归子/父查询 [英] Recursive Child/Parent queries in T/SQL

问题描述

我在 Microsoft SQL Server 2008 中使用 T/SQL

I am using T/SQL in Microsoft SQL Server 2008

我有一张桌子

CREATE TABLE [TestTable](
[CHILD] [int] NOT NULL,
[PARENT] [int] NOT NULL
) ON [PRIMARY]

GO

这些是定义父子层次关系的一些值

These are some values which define a parent child hierarchial relationship

CHILD PARENT
1       2
2       0
3       1
4       2
5       0

从视觉上看，这张桌子是这样的

Visually, this table looks like this

0
   2
      1
         3
      4
   5

理想情况下，我希望值显示如下(其中右侧栏表示世代)

I would ideally like the values to be shown as follows (where the right hand column indicates the generation)

CHILD    GENERATION
 0          0
 2          1
 1          2
 3          3
 4          2
 5          1

我的 T/SQL 代码如下

My T/SQL code looks like this

with n(CHILD, PARENT, GENERATION) as (
select CHILD, PARENT,1 as GENERATION from TestTable
where PARENT=0 
union all
select nplus1.CHILD, nplus1.PARENT, GENERATION+1 from TestTable as nplus1, n
where nplus1.PARENT=n.CHILD 
)
select CHILD,GENERATION from n

然而它不起作用！

它回来了

CHILD   GENERATION
2        1
5        1
1        2
4        2
3        3

它具有正确的生成，但排序顺序错误！有没有人有任何想法如何解决这个问题?

It has the right generation, but the wrong sort order! Does anyone have any ideas how to solve this?

谢谢！

推荐答案

你需要你的递归来构建一些可以在最后排序的东西:

You'll need your recursion to also build something that can be sorted by at the end:

declare @t TABLE (
[CHILD] [int] NOT NULL,
[PARENT] [int] NOT NULL
) 

insert @t values
( 0, -1),   -- I added this
( 1, 2 ),
( 2, 0 ),
( 3, 1 ),
( 4, 2 ),
( 5, 0 )

(注意我添加了一个真正的根元素)

(note I have added a true root element)

;with n(CHILD, PARENT, GENERATION, hierarchy) as (
select CHILD, PARENT,0, CAST(CHILD as nvarchar) as GENERATION from @t
where PARENT=-1
union all
select nplus1.CHILD, nplus1.PARENT, GENERATION+1, 
cast(n.hierarchy + '.' + CAST(nplus1.child as nvarchar) as nvarchar)
 from 
@t as nplus1 inner join n on nplus1.PARENT=n.CHILD 
)
select CHILD,GENERATION
from n
order by hierarchy

返回

CHILD       GENERATION
----------- -----------
0           0
2           1
1           2
3           3
4           2
5           1

包括用于说明的 hierarchy:

CHILD       GENERATION  hierarchy
----------- ----------- ------------------------------
0           0           0
2           1           0.2
1           2           0.2.1
3           3           0.2.1.3
4           2           0.2.4
5           1           0.5

根据您的 id 有多大，您可能需要使用零进行左填充才能正确排序.

Depending on how big your ids get, you might have to do stuff with left-padding with zeroes to get the sorting right.

请注意，SQL 2008 有一个内置的 hierarchy 类型来处理这种事情......

Note that SQL 2008 has a built-in hierarchy type for this kind of thing...

这篇关于T/SQL 中的递归子/父查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！