递归树查询

墨蓝 2022-09-29 13:56 41阅读 0赞

--表结构
    CREATE TABLE [dbo].[PSY_Web_MenuClass](
     [ModuleCode] [varchar](32) NOT NULL,
     [ModuleName] [varchar](128) NOT NULL,
     [ShortModuleName] [varchar](64) NULL,
     [LevelID] [int] NOT NULL,
     [ParentCode] [varchar](32) NULL
     CONSTRAINT [PK_PSY_WEB_MENUCLASS] PRIMARY KEY NONCLUSTERED 
    (
     [ModuleCode] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    /* ModuleCode 根节点，ParentCode 父节点*/
    
    CREATE FUNCTION f_MenuSort(@ModuleCode varchar(32)=NULL,@sort int=1)
    RETURNS @t_Level TABLE(ModuleCode varchar(32),sort int)
    AS
    BEGIN
     DECLARE cod_cur CURSOR LOCAL
     FOR
     SELECT ModuleCode FROM PSY_Web_MenuClass
     WHERE ParentCode=@ModuleCode OR (@ModuleCode IS NULL AND ParentCode IS NULL)
     OPEN cod_cur
     FETCH cod_cur INTO @ModuleCode
     WHILE @@FETCH_STATUS=0
     BEGIN
      INSERT @t_Level VALUES(@ModuleCode,@sort)
      SET @sort=@sort+1
      IF @@NESTLEVEL<32 --如果递归(嵌套查询)层数未超过32层(递归(嵌套查询)最大允许32层)
      BEGIN
      --开始递归查找当前节点的子节点
       INSERT @t_Level SELECT * FROM f_MenuSort(@ModuleCode,@sort)
       SET @sort=@sort+@@ROWCOUNT  --排序号加上子节点个数
      END
      FETCH cod_cur INTO @ModuleCode
     END
     RETURN
    END
    GO
    
    SELECT a.*
    FROM PSY_Web_MenuClass a,f_MenuSort('MYSUN',2) b
    WHERE a.ModuleCode=b.ModuleCode
    
    
    --------------------------------------------------2
    CREATE FUNCTION f_LPad
    ( 
    @i int,@len int 
    ) 
    RETURNS nvarchar(max) 
    AS 
    BEGIN 
    RETURN cast (replicate('0', @len - len(@i) ) + convert(nvarchar,@i) as nvarchar(max)) 
    END 
    
     
    
    CREATE PROCEDURE PSYP_GetMenuTree
    ----单个父节点
    @MenuCode varchar(32)=NULL 
    AS 
    BEGIN 
    with ReplicateMenu 
    as 
    ( 
    select *,0 as Level,cast('0' as nvarchar(max)) as TreePath from PSY_Web_MenuClass where ModuleCode = @MenuCode 
    union all 
    select A.*,rm.Level + 1,rm.TreePath + dbo.f_LPad(Row_Number() over (order by A.ModuleCode desc),8) as TreePath from PSY_Web_MenuClass as A inner join ReplicateMenu as rm on A.ParentCode = rm.ModuleCode ) 
    SELECT * from ReplicateMenu  
    END
    
    ----单个父节点
    
    
    Create PROCEDURE PSYP_GetMenuTree
    ---多父节点
    @MenuCode varchar(32)=NULL 
    AS 
    BEGIN 
    with ReplicateMenu 
    as 
    ( 
    select *,0 as Level,cast(Row_Number() over (order by A.ModuleCode desc) as nvarchar(max)) as TreePath from PSY_Web_MenuClass where ModuleCode = @MenuCode 
    union all 
    select csc.*,rm.Level + 1,rm.TreePath + dbo.Lpad(Row_Number() over (order by A.ModuleCode desc),8) as TreePath from  PSY_Web_MenuClass as A inner join ReplicateMenu as rm on A.ParentCode = rm.ModuleCode ) 
    SELECT * from ReplicateMenu
    END 
    ---多父节点
    
    
    exec psyp_GetMenuTree 'MYSUN'