在父子关系表中获取子孙后代结点数据SQL查询算法

常见于目录表结构中（树状结构）如项目目录表，根据目录Id获取其属下所有子结点：

CREATE    PROCEDURE [dbo].[pGetDescendedPhysicalItemCatalogs](
    @PhysicalItemCatalogId int 
)
AS
set nocount on

BEGIN TRY
    IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##PhysicalItemCatalog')
        CREATE TABLE ##PhysicalItemCatalog(
            [PhysicalItemCatalogId] [int] ,
            [Name] [nvarchar](50) NOT NULL ,
            [MnemonicCode] [nvarchar](10) NOT NULL ,
            [ParentId] [int] NOT NULL ,
            [IsDeleted] [bit] NOT NULL ,
            [IsValid] [bit] NOT NULL ,
            [PhysicalSpecialtyId] [int] NOT NULL ,
            [Handled] [bit] NOT NULL default 0
        )

    INSERT ##PhysicalItemCatalog(PhysicalItemCatalogId, Name, MnemonicCode, ParentId, IsDeleted, IsValid, PhysicalSpecialtyId)
    SELECT PhysicalItemCatalogId, Name, MnemonicCode, ParentId, IsDeleted, IsValid, PhysicalSpecialtyId 
    FROM entity.PhysicalItemCatalog with(nolock) WHERE PhysicalItemCatalogId > -1 AND ParentId = @PhysicalItemCatalogId
    
    DECLARE @catalogId int 
    SELECT TOP 1 @catalogId = PhysicalItemCatalogId FROM ##PhysicalItemCatalog WHERE Handled = 0
    IF @catalogId IS NOT NULL 
    begin
        update ##PhysicalItemCatalog set Handled = 1 where PhysicalItemCatalogId = @catalogId
        exec [dbo].[pGetDescendedPhysicalItemCatalogs] @catalogId
    end
    ELSE
    begin
        SELECT * FROM ##PhysicalItemCatalog
        DROP TABLE ##PhysicalItemCatalog
    end
END TRY
BEGIN CATCH
    IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##PhysicalItemCatalog')
        DROP TABLE ##PhysicalItemCatalog
END CATCH

set nocount off

 

使用的场景（譬如更新与根目录不一致的字段值）：

CREATE    PROCEDURE [dbo].[pRecalPhysicalItemCatalogsSpecialty]
AS
set nocount on

declare @catalogId int
declare @specialtyId int
DECLARE @catalog TABLE (
    [PhysicalItemCatalogId] [int] ,
    [Name] [nvarchar](50) NOT NULL ,
    [MnemonicCode] [nvarchar](10) NOT NULL ,
    [ParentId] [int] NOT NULL ,
    [IsDeleted] [bit] NOT NULL ,
    [IsValid] [bit] NOT NULL ,
    [PhysicalSpecialtyId] [int] NOT NULL ,
    [Handled] [bit] NOT NULL default 0
)

DECLARE my_cursor CURSOR FOR
select PhysicalItemCatalogId, PhysicalSpecialtyId from entity.PhysicalItemCatalog where parentId = -1
and PhysicalItemCatalogId in (select ParentId from entity.PhysicalItemCatalog where ParentId > -1)
OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @catalogId, @specialtyId;
WHILE @@FETCH_STATUS = 0
BEGIN
    insert into @catalog exec [dbo].[pGetDescendedPhysicalItemCatalogs] @catalogId

    UPDATE t
    SET PhysicalSpecialtyId = @specialtyId 
    FROM entity.PhysicalItemCatalog t, @catalog a
    WHERE t.PhysicalItemCatalogId = a.PhysicalItemCatalogId

    delete @catalog

    FETCH NEXT FROM my_cursor INTO @catalogId, @specialtyId;
END;
CLOSE my_cursor;
DEALLOCATE my_cursor;

set nocount off

上述可能存在嵌套不能超过32层的错误问题，修改将查询结果保存到临时表中：

/*
* TEST SCRIPT:
    declare @catalogId int
    DECLARE my_cursor CURSOR FOR
    select MedicineCatalogId from mmat.MedicineCatalog with(nolock) WHERE MedicineCatalogId > -1 AND ParentCatalogId = -1

    OPEN my_cursor;
    FETCH NEXT FROM my_cursor INTO @catalogId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        exec [dbo].[pGetChildrenMedicineCatalogs] @catalogId
        FETCH NEXT FROM my_cursor INTO @catalogId;
    END;

    CLOSE my_cursor;
    DEALLOCATE my_cursor;
    
    IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##MedicineCatalog')
    BEGIN
        SELECT * FROM ##MedicineCatalog ORDER BY ParentCatalogId, MedicineCatalogId
        DROP TABLE ##MedicineCatalog
    END
*/
CREATE    PROCEDURE [dbo].[pGetChildrenMedicineCatalogs](
    @MedicineCatalogId int
)

AS
set nocount on
IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##MedicineCatalog')
    CREATE TABLE ##MedicineCatalog(
        [MedicineCatalogId] [int] ,
        [Name] [nvarchar](50) NOT NULL ,
        [SubstanceClassCodeId] [tinyint] NOT NULL ,
        [SpellCode] [nvarchar](20) NULL ,
        [ParentCatalogId] [int] NOT NULL ,
        [IsDeleted] [bit] NOT NULL ,
        [Handled] [bit] NOT NULL default 0
    )

DECLARE @MedicineCatalog TABLE (
    [RID] INT IDENTITY(1,1),
    [MedicineCatalogId] [int] ,
    [Name] [nvarchar](50) NOT NULL ,
    [SubstanceClassCodeId] [tinyint] NOT NULL ,
    [SpellCode] [nvarchar](20) NULL ,
    [ParentCatalogId] [int] NOT NULL ,
    [IsDeleted] [bit] NOT NULL ,
    [Handled] [bit] NOT NULL default 0
)
 
INSERT ##MedicineCatalog(MedicineCatalogId, Name, SubstanceClassCodeId, SpellCode, ParentCatalogId, IsDeleted)
SELECT MedicineCatalogId, Name, SubstanceClassCodeId, SpellCode, ParentCatalogId, IsDeleted 
FROM mmat.MedicineCatalog with(nolock) WHERE MedicineCatalogId > -1 AND ParentCatalogId = @MedicineCatalogId

declare @count INT

INSERT @MedicineCatalog(MedicineCatalogId, Name, SubstanceClassCodeId, SpellCode, ParentCatalogId, IsDeleted)
SELECT MedicineCatalogId, Name, SubstanceClassCodeId, SpellCode, ParentCatalogId, IsDeleted 
FROM mmat.MedicineCatalog with(nolock) WHERE MedicineCatalogId > -1 AND ParentCatalogId = @MedicineCatalogId

SELECT @count = @@ROWCOUNT;
declare @catalogId INT
declare @rid INT
SET @rid = 1;
WHILE @rid <= @count
BEGIN
    SELECT @catalogId = MedicineCatalogId FROM @MedicineCatalog mc WHERE RID = @rid;
    update ##MedicineCatalog set Handled = 1 where MedicineCatalogId = @catalogId
    exec [dbo].[pGetChildrenMedicineCatalogs] @catalogId

    SET @rid = @rid + 1;
END

set nocount off
GO