SQL：搜索节点之子子孙孙_sql 查询子节点孙子节点-优快云博客

本文介绍了一个SQL Server存储过程，该过程通过递归查询来获取指定项目的子项目及其层级信息。此过程首先定义一个临时表存储初始项目ID及层级，然后通过循环递归查询直至没有更多子项目为止。

CREATE FUNCTION [dbo].[pyx_fn_getX] ( @id INT )
RETURNS @tbl TABLE ( ID INT, Parent INT )
AS
BEGIN
DECLARE @tb_id TABLE ( ID INT, lvls INT )
DECLARE @lvl INT

SET @lvl = 1
INSERT INTO @tb_id
          SELECT @id,
                  @lvl

WHILE EXISTS ( SELECT TOP 1
                        projectID
                 FROM   HDSSortproject
                 WHERE Parent_projectID IN ( SELECT ID
                                              FROM    @tb_id
                                              WHERE   lvls = @lvl ) )
    BEGIN
      SET @lvl = @lvl + 1
      INSERT INTO @tb_id
              SELECT projectID,
                      @lvl
              FROM    HDSSortproject
              WHERE   Parent_projectID IN ( SELECT ID
                                            FROM    @tb_id
                                            WHERE   lvls = @lvl - 1 )
    END

INSERT INTO @tbl
          SELECT projectID,
                  Parent_projectID
          FROM    HDSSortproject
          WHERE   projectID IN ( SELECT id
                                 FROM   @tb_id )
RETURN
END