SQL：搜索节点之子子孙孙

来源：互联网发布：淘宝客站内违规推广编辑：程序博客网时间：2024/06/15 22:49

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