T-SQL - 如何获取分层表中的给定元素的所有后代

• 下载脚本 - 1.24 KB

引言

在电子商务网站中，经常需要检索给定类别下的所有产品，包括所有后代类别。本文提供了一个 T-SQL 表值函数，提供了一个简单的解决方案。我们假设类别是在一个具有自引用外键的层次表中定义的。

使用代码

首先，我们需要创建层次表（为了保持简单，我们使用 Name 作为表键）

CREATE TABLE [dbo].[Category](
    [ParentName] [varchar](20) NULL,
    [Name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
    [Name] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Category]  ADD  
CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentName])
REFERENCES [dbo].[Category] ([Name])
GO

然后，我们创建一个递归存储过程，用于填充表中的测试数据

CREATE PROC spCreateChilds

@ParentName    varchar(20),
@BaseName        varchar(20),
@Level        integer,
@MaxDepth        integer,
@ChildNumber    integer
        
AS

IF @Level < @MaxDepth
 BEGIN

  DECLARE @I Integer

  SET @I = 1

  WHILE (@I < @ChildNumber AND @ParentName IS NOT NULL) OR @I = 1
   BEGIN

    INSERT INTO Category
    (Name, ParentName)
    VALUES        
    (ISNULL(@BaseName, '') + CASE WHEN 
          @BaseName IS NULL THEN '' ELSE '.' END  
          + CAST(@I as varchar), @ParentName)

    DECLARE @ExecParentName varchar(20)
    DECLARE @ExecBaseName varchar(20)
    DECLARE @ExecLevel int

    SET @ExecParentName = ISNULL(@BaseName, '') + 
         CASE WHEN @BaseName IS NULL 
     THEN '' ELSE '.' END  + CAST(@I as varchar)
    SET @ExecBaseName = ISNULL(@BaseName, '') 
         + CASE WHEN @BaseName IS NULL THEN '' ELSE '.' END  + 
         CAST(@I as varchar)
    SET @ExecLevel = @Level +1

    EXECUTE [dbo].[spCreateChilds] 
            @ExecParentName
           ,@ExecBaseName
           ,@ExecLevel
           ,@MaxDepth
           ,@ChildNumber

    SET @I = @I + 1

 END                
END

当然，我们调用它！

EXECUTE [dbo].[spCreateChilds] 
            NULL
           ,NULL
           ,1
           ,4  -- 4 hierarchical levels
           ,30 -- 30 childs per category

现在，我们的表充满了测试数据，是时候定义我们的函数了。

第一个函数检索所有祖先，而且非常简单

CREATE FUNCTION [dbo].[GetAscendantCategories] 
(    
    @CategoryName varchar(20)
)
RETURNS @Result TABLE  (Name varchar(20))
AS
    BEGIN

    WHILE @CategoryName IS NOT NULL
        BEGIN
            INSERT INTO @Result
            SELECT    @CategoryName
            
            SELECT     @CategoryName = ParentName
            FROM     dbo.Category
            WHERE   Name = @CategoryName
    
        END
    
    RETURN     
END

第二个函数是我们实际将要使用的函数。它检索行的所有后代

CREATE FUNCTION [dbo].[GetDescendantCategories] 
(    
    @CategoryName varchar(20)
)
RETURNS @Result TABLE  (Name varchar(20))
AS
    BEGIN

    INSERT INTO @Result
    SELECT 
            Name
    FROM     
            dbo.Category C
    WHERE     @CategoryName in (
                       SELECT P.Name 
                       FROM GetAscendantCategories(C.Name) P)
    
    RETURN     
END

现在，我们可以测试它，传递一个测试键！

SELECT * 
FROM [dbo].[GetDescendantCategories] ('1.2')

在产品检索查询中使用此函数很容易。这是一个例子（假设我们有一个名为 Product 的表，其中包含一个字段 CategoryName 以及从该字段到 Category 表的外键）

SELECT *
FROM Product P
INNER JOIN [dbo].[GetDescendantCategories] ('1.2') C
ON P.CategoryKey = C.Name

优化

好的，这很酷，但我意识到这个解决方案真的很慢。如果我们使用公共表表达式，会更好。这是新的函数（感谢 Eddie de Bears）

CREATE FUNCTION [dbo].[GetDescendantCategories]
(
    @CategoryName varchar(20)
)
RETURNS @Result TABLE (Name varchar(20)) AS BEGIN


    WITH Result (Name)
    AS
    (
        SELECT Name
        FROM Category
        WHERE Name = @CategoryName
        UNION ALL
        SELECT C.Name FROM Category C
        INNER JOIN Result R ON C.ParentName = R.Name
    )
    INSERT INTO @Result
    SELECT Name
    FROM Result

    RETURN

END