广度优先,从子节点找到到根路径的sql

by Weijie JIN on

这是以前在公司遇到的一个问题,避免sql语句的递归,提高效率

/*
注解:

以前遇到过一个同事,在处理树的时候,在sql语句里面用递归,造成性能非常低下。

在遇到sql处理树的时候,可以采用以下方法,用循环来解决。

主要思路:

找到Cateogry的Parent插入临时表,在临时表里做遍历,每到一条记录,都做一个操作:将它的parent select出来,插入临时表,最后,将临时表join Cateogry表。

*/

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Proc_GetCategoryPath]
@CategoryID uniqueidentifier
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID=object_id(N'#_Name') AND OBJECTPROPERTY(id, N'IsUserTable')=1)
    DROP TABLE #_Name

/*建一个临时表*/
CREATE TABLE [dbo].[#_Name] (
    [ID] [bigint] IDENTITY (1, 1) NOT NULL,
    [ParentCategoryID] uniqueidentifier NULL ,
    [CategoryID] uniqueidentifier NULL ,
    [OrderNum] [int] NULL
) ON [PRIMARY]


/*do insert*/

INSERT INTO #_Name
SELECT
[Category].[ParentCategoryID],
[Category].[ID] AS CateogryID,
[Category].[OrderNum]
FROM
[Category]
WHERE
[Category].[ID] = @CategoryID
order by [Category].[OrderNum]

DECLARE @ID BIGINT
SET @ID = 1
DECLARE @Parent uniqueidentifier
SET @Parent = (SELECT TOP 1 [ParentCategoryID] FROM #_Name WHERE ID = @ID)

WHILE (@Parent IS NOT NULL and @Parent <> '00000000-0000-0000-0000-000000000000')
BEGIN
    INSERT INTO #_Name
    SELECT
    [Category].[ParentCategoryID],
    [Category].[ID] as CategoryID,
    [Category].[OrderNum]
    FROM
    [Category]
    WHERE
    [Category].[ID] = @Parent
    order by [Category].[OrderNum]

    SET @ID = @ID + 1
    SET @Parent = (SELECT TOP 1 [ParentCategoryID] FROM #_Name WHERE ID = @ID)
END


/*end do insert*/

SELECT [Category].*
FROM #_Name
JOIN Category ON [#_Name].[CategoryID] = [Category].[ID]  

Previous post:

Next post: