具有父-子关系的递归查询

Question

我正在尝试在Server中进行递归查询，该查询以分层方式显示数据。这是表的结构

    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar(100)] NOT NULL,
    [Parent_Id] [int] NULL,

每个产品都有一个父级。列Parent_Id包含父类的id。对于根产品，parent_id是空的。

我想要创建一个sql查询，该查询以分层方式显示产品。下面的图片是如何组织产品的一个例子。

​

​

产品可以有孩子的产品。

对于上面的图片，查询结果应该如下所示：

id name      parent_id
1  P1        NULL
2  P2        NULL
3  P2-1      2
4  P2-2      2
5  P2-3      2
6  P2-3-1    5
7  P2-3-2    5
8  P3        NULL
9  P3-1      8

以下是我为实现这一目标而写的请求：

with tree as (select * from products
               union all
               select * from tree where parent_id = tree.id
             )
select * from tree;

但我得到的结果与以下所示相似：

1  P1        NULL
2  P2        NULL
8  P3        NULL
3  P2-1      2
4  P2-2      2
5  P2-3      2
9  P3-1      8
6  P2-3-1    5
7  P2-3-2    5

我想要的是分组每个产品兄弟，以便每个产品显示在它的直接父级.下。

Answer 1

只是使用数据类型hierarchyid的另一个选项

还有一些与层次化相关的其他特性和功能

示例

-- Optional See 1st WHERE
Declare @Top int = null  --<<  Sets top of Hier Try 2

;with cteP as (
      Select ID
            ,parent_id 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(parent_id ,-1) else ID end
      --Where parent_id is null  -- Use this where if you always want the full hierarchy
      Union  All
      Select ID  = r.ID
            ,parent_id  = r.parent_id 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   YourTable r
      Join   cteP p on r.parent_id  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,parent_id
      ,Name  
 From cteP A
 Order By A.HierID

结果

Lvl ID  parent_id   Name
1   1   NULL        P1
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2
1   8   NULL        P3
2   9   8           P3-1

只是为了好玩，如果我将@Top设置为2，结果将是

Lvl ID  parent_id   Name
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2

Answer 2

在递归查询中构造路径。以下操作作为具有固定长度ids的字符串执行：

with tree as (
      select p.id, p.name, p.parentid,
             format(p.parentid, '0000') as path
      from products p
      where p.parentid is null
      union all
      select p.id, p.name, p.parentid,
             concat(cte.path, '->', format(p.id, '0000')
      from tree join
           products p
      where p.parent_id = t.id
     )
select *
from tree;

Answer 3

如果我理解这是正确的，而你有你想要的结果，但只是无序，你应该能够按名称排序结果。

with tree as (select * from products
               union all
               select * from tree where parent_id = tree.id
             )
select * from tree order by name asc;