时间戳

首页 > 解决方案 > 具有父子关系的递归查询

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

问题描述

我正在尝试在 SQL 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

我想要的是对每个产品兄弟姐妹进行分组，以便每个产品都显示在其直接父级下。

标签： sqlsql-servertsqlhierarchical-datarecursive-query

解决方案

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

还有一些与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

推荐阅读