--表结构
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'