DotNet 笔记

东方欲晓,莫道君起早。

SqlServer树形表结构,获取某(行)“节点的深度”及“后辈节点集合”

1:例如表Tb_Menu,结构如下:

id(int)    parentid(int)    title(nvarchar)

2:获取某个行的深度代码如下:

CREATE FUNCTION [dbo].[Fun_GetMenuDeep] ( @id INT )
RETURNS INT
AS 
    BEGIN
	
        DECLARE @deep INT
        DECLARE @tempid INT 
        SET @deep = 0

        WHILE @id != 0 
            BEGIN
                SELECT  @tempid = parentid  
                FROM    tb_menu --查询的目标表
                WHERE   id = @id 
                IF @tempid >= 0
                    AND @tempid != @id 
                    BEGIN
                        SET @id = @tempid
                        SET @deep = @deep + 1 
                    END 
                ELSE 
                    BEGIN 
                        SET @id = 0
                    END
            END
        RETURN @deep

    END

3:根据id,获取某行的所有后代以及其深度

-- =============================================
-- Author:		<zhifeiya>
-- Create date: <2014-11-30>
-- Description:	<根据父id 获取其下的所有后辈,并包含当前后辈的深度>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_GetMenuTreeList] @pid INT
AS 
    BEGIN
        WITH    TREE
                  AS ( SELECT   *
                       FROM     Tb_Menu
                       WHERE    parentid = @pid  -- 要查询的父 id 
                       UNION ALL
                       SELECT   Tb_Menu.*
                       FROM     Tb_Menu ,
                                TREE
                       WHERE    Tb_Menu.parentid = TREE.id
                     )
            SELECT  t.* ,
                    dbo.fun_GetMenuDeep(t.id) AS deep
            FROM    TREE AS t
    END
GO


 

 

Loading