SQL 递归生成树

来源：互联网发布：音频制作软件中文版编辑：程序博客网时间：2024/05/19 11:20

捡到一个题目，题目如下，不清楚自己做的对于不对，还请各位看官指正。

题目：

构建数据表smMenu，主要字段有：ID、菜单名、菜单类名、上级菜单ID

测试用初始数据如下：

销售管理

报价管理

制订报价单

报价单查询

订单管理

订单录入

订单审核

计划管理

主计划

MRP计划

用递归算法将菜单数据插入到原生Tree控件，显示所有菜单（按上下级结构显示）

要求写出递归函数的定义及简单调用代码。

USE E8RFIDEnggo/*************************************************************** creator:JustinYang DateTime 2016-03-26** function:针对表生成树** 传入参数： 无** 输出参数：@smMenu****************************************************************/create procedure sp_MakeTeeeas beginUPDATE smMenu SET upperid = id WHERE upperid IS NULL OR upperid = 0    DECLARE @smMenu Table(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)--遍历所有根节点,其中调用递归函数生成树DECLARE maketree CURSOR FAST_FORWARD FOR --建立游标SELECT id FROM smMenu WITH (NOLOCK)WHERE ISNULL(treeid, 0) = 0 AND upperid = id ORDER BY upperid ASC, id ASCOPEN maketreeDECLARE @id INTDECLARE @childorderid INTSET @childorderid = 0DECLARE @treeid VARCHAR(100)DECLARE @sId VARCHAR(2000) --已经处理过的节点SET @sid = ','DECLARE @streeid VARCHAR(2000) --已经处理过的节点树节点，与@sid一一对应SET @streeid = ','FETCH NEXT FROM maketree INTO @idWHILE @@fetch_status = 0BEGINIF CHARINDEX(',' + CAST(@id AS VARCHAR(10)) + ',',@sid) < 0 BEGINSET @childorderid = @childorderid + 1SET @treeid = CAST(@childorderid AS VARCHAR(3))WHILE (LEN(@treeid) < 2) BEGINSET @treeid = '0' + @treeidENDSET @treeid = '1' + @treeid--将当前节点加入已经处理节点SET @sId = @sID + CAST(@id AS VARCHAR(10)) + ','SET @streeid = @streeid + @treeid + ','            --递归处理子节点            INSERT INTO @smMenu(id,treeid,menuNM,upperMenu,upperid)             SELECT id,treeid,menuNM,upperMenu,upperid FROM dbo.diGuiMakeTree(@id, @treeid, @sId)                        --加入根节点            INSERT INTO @smMenu                    ( id ,                      treeid ,                      menuNM ,                      upperMenu ,                      upperid                    )            VALUES  ( @id , -- id - int                      @treeid , -- treeid - varchar(6000)                      '' , -- menuNM - varchar(50)                      '' , -- upperMenu - varchar(50)                      0  -- upperid - int                    )            ENDFETCH NEXT FROM maketree INTO @idENDSELECT * FROM @smMenu--返回需要的结果，treeid即为树idCLOSE maketreeDEALLOCATE maketreeENDGO/********************************************************* creator:JustinYang DateTime:2016-03-26** function:处理树的当前节点,获取** 输入参数：@iid当前节点ID**           @itreeid 当前节点树ID**           @sId 已经处理过的节点  用于优化作用** 输出参数：@smMenu表          ** 调用方式：Select dbo.diGuiMakeTree()*************************************************************/create function diGuiMakeTree(   @iid int,--当前节点   @itreeid INT,   @sId varchar(2000) )RETURNS @maketreetable TABLE(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)asbegin    --DECLARE @maketreetable Table(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)   declare maketree cursor FAST_FORWARD for --建立游标SELECT id FROM smMenu WITH (NOLOCK) WHERE ISNULL(treeid, 0) = 0 AND upperid = @iid AND CHARINDEX(','+CAST(@iid AS VARCHAR(10))+',',@sId)<0 ORDER BY upperid ASC, id ASCOPEN maketreeDECLARE @childorderid INTSET @childorderid = 0--兄弟节点个数DECLARE @id INT --当前处理节点FETCH NEXT FROM maketree INTO @idWHILE @@fetch_status = 0BEGINIF (CHARINDEX(',' + CAST(@id AS VARCHAR(10)) + ',',@sid) > 0)BEGINDECLARE @treeid VARCHAR(100)--当前节点对应的树节点                        --计算treeidSET @childorderid = @childorderid + 1SET @treeid = CAST(@childorderid AS VARCHAR(3))WHILE (LEN(@treeid) < 3)BEGINSET @treeid = '0' + @treeidENDSET @treeid = @itreeid + @treeidSET @sId = @sId + CAST(@id AS VARCHAR(10)) + ','            --递归处理子节点            INSERT INTO @maketreetable(id,treeid,menuNM,upperMenu,upperid)             SELECT id,treeid,menuNM,upperMenu,upperid FROM dbo.diGuiMakeTree(@id, @treeid, @sId)                        --处理当前节点            INSERT INTO @maketreetable                    ( id ,                      treeid ,                      menuNM ,                      upperMenu ,                      upperid                    )            VALUES  ( @id , -- id - int                      @treeid , -- treeid - varchar(6000)                      '' , -- menuNM - varchar(50)                      '' , -- upperMenu - varchar(50)                      @iid  -- upperid - int                    )            END--跳转到下一个兄弟节点FETCH NEXT FROM maketree INTO @id     ENDCLOSE maketreeDEALLOCATE maketreeRETURNEND

真诚欢迎指正！

1 0