无限级分类 父节点 子节点

创建：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Class]
GO
Create TABLE [dbo].[Class] (
[Class_Id] [int] NOT NULL ,
[Class_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Parent_ID] [int] NULL ,
[Class_Path] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Class_Depth] [int] NULL ,
[Class_Order] [int] NULL ,
[Class_Intro] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
Alter TABLE [dbo].[Class] ADD
CONSTRAINT [DF_Class_Parent_ID] DEFAULT (0) FOR [Parent_ID],
CONSTRAINT [DF_Class_Class_Depth] DEFAULT (0) FOR [Class_Depth],
CONSTRAINT [DF_Class_Class_Order] DEFAULT (0) FOR [Class_Order],
CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED
(
[Class_Id]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'分类深度(默认值0）', N'user', N'dbo', N'table', N'Class', N'column', N'Class_Depth'
GO
exec sp_addextendedproperty N'MS_Description', N'int 主键（注：非标识）', N'user', N'dbo', N'table', N'Class', N'column', N'Class_Id'
GO
exec sp_addextendedproperty N'MS_Description', N'分类说明', N'user', N'dbo', N'table', N'Class', N'column', N'Class_Intro'
GO
exec sp_addextendedproperty N'MS_Description', N'分类名称', N'user', N'dbo', N'table', N'Class', N'column', N'Class_Name'
GO
exec sp_addextendedproperty N'MS_Description', N'排序(默认值0)', N'user', N'dbo', N'table', N'Class', N'column', N'Class_Order'
GO
exec sp_addextendedproperty N'MS_Description', N'分类路径', N'user', N'dbo', N'table', N'Class', N'column', N'Class_Path'
GO
exec sp_addextendedproperty N'MS_Description', N'父分类ID（默认值0）', N'user', N'dbo', N'table', N'Class', N'column', N'Parent_ID'

 

 

增加：

Create PROCEDURE sp_Class_Insert
(
@Parent_ID int,
@Class_Name nvarchar(50),
@Class_Intro nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
--通过现有记录获取栏目ID
Declare @Class_Id As int
Declare @Class_Depth As int
Select @Class_Id = Max(Class_Id) From Class
IF @Class_Id Is Not Null
Set @Class_Id = @Class_Id+1
Else
Set @Class_Id = 1
--判断是否是顶级栏目，设置其Class_Path和Class_Order
Declare @Class_Path As nvarchar(1000)
Declare @Class_Order As int
IF @Parent_ID = 0
Begin
Set @Class_Path =Ltrim(Str(@Class_Id))
Select @Class_Order = Max(Class_Order) From Class
IF @Class_Order Is Not Null
Set @Class_Order = @Class_Order + 1
Else --如果没有查询到记录，说明这是第一条记录
Set @Class_Order = 1
--深度
Set @Class_Depth = 1
End
Else
Begin
--获取父节点的路径和深度
Select @Class_Path = Class_Path ,@Class_Depth = Class_Depth From Class Where
Class_Id=@Parent_ID
IF @Class_Path Is Null
Begin
Set @Err = 1
Goto theEnd
End
--获取同父节点下的最大序号
Select @Class_Order = Max(Class_Order) From Class Where Class_Path like
''+@Class_Path+'|%' or Class_Id = @Parent_ID
IF @Class_Order Is Not Null --如果序号存在，那么将该序号后的所有序号都加1
Begin
--更新当前要插入节点后所有节点的序号
Update Class Set Class_Order = Class_Order +1 Where Class_Order
>@Class_Order
--同父节点下的最大序号加上1，构成自己的序号
Set @Class_Order = @Class_Order + 1
End
Else
Begin
Set @Err=1
Goto theEnd
End
--父节点的路径加上自己的ID号，构成自己的路径
Set @Class_Path = @Class_Path + '|' + Ltrim(Str(@Class_Id))
--深度
Set @Class_Depth = @Class_Depth+1
End
Insert Into Class(Class_Id,Class_Name,Parent_ID,Class_Path,Class_Depth,Class_Order,Class_Intro)
Values(@Class_Id,@Class_Name,@Parent_ID,@Class_Path,@Class_Depth,@Class_Order,@Class_Intro)
IF @@Error<>0
Begin
Set @Err=1
Goto theEnd
End
--更新当前记录之后的记录的ORDER
--Update Class Set Class_Order = Class_Order+1 Where Class_Order > @Class_Order
theEnd:
IF @Err=0
Begin
Commit Tran
Return @Class_Id
End
Else
Begin
Rollback Tran
Return 0
End
GO

 

删除：

 

Create PROCEDURE sp_Class_Delete
(
@Class_Id int
)
AS
Declare @Err As int
Set @Err = 0
Begin Tran
--首先查询该节点下是否有子节点
Select Class_Id From Class Where Parent_ID = @Class_Id
IF @@RowCount<>0
Begin
Set @Err = 1
Goto theEnd
End
--获取该节点的Class_Order,为了删除后整理其他记录的顺序
Declare @Class_Order As int
Select @Class_Order = Class_Order From Class Where Class_Id = @Class_Id
IF @Class_Order Is NUll
Begin
Set @Err =2
Goto theEnd
End
--更新其他记录的Class_Order
Update Class Set Class_Order = Class_Order -1 Where Class_Order >@Class_Order
IF @@Error<>0
Begin
Set @Err =3
Goto theEnd
End
--删除操作
Delete From Class Where Class_Id=@Class_Id
IF @@Error<>0
Begin
Set @Err =4
Goto theEnd
End
--更新其他记录的Class_Id
--Update Class Set Class_Id= Class_Id - 1 Where Class_Id >@Class_Id
--IF @@Error<>0
-- Begin
-- Set @Err =5
-- Goto theEnd
-- End
--
theEnd:
IF @Err = 0
Begin
Commit Tran
Return 0 --删除成功
End
Else
Begin
IF @Err=1
Begin
Rollback Tran
Return 1 --有子节点
End
Else
Begin
Rollback Tran
Return 2--未知错误
End
End
GO

 

 

 

 

更新：

Create PROCEDURE sp_Class_Update
(
@Class_Id int,
@Parent_ID int,
@Class_Name nvarchar(50),
@Class_Intro nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
--获取修改前的：Parent_ID,Class_Depth,Class_Order
Declare @oParent_ID As int
Declare @oClass_Depth As int
Declare @oClass_Order As int
Declare @oClass_Path As nvarchar(1000)
Select @oParent_ID = Parent_ID, @oClass_Depth = Class_Depth,@oClass_Order = Class_Order, @oClass_Path = Class_Path From Class Where Class_Id = @Class_Id
IF @oParent_ID Is Null
Begin
Set @Err = 1
Goto theEnd
End
--如果父ID没有改变，则直接修改栏目名和栏目简介
IF @oParent_ID = @Parent_ID
Begin
Update Class Set Class_Name = @Class_Name,Class_Intro = @Class_Intro Where Class_Id = @Class_Id
IF @@Error <> 0
Set @Err = 2
Goto theEnd
End
Declare @nClass_Path As nvarchar(1000)
Declare @nClass_Depth As int
Declare @nClass_Order As int
--获取当前节点作为父节点所包含的节点数[包括自身] 注：如果返回 “1” 说明是单节点
Declare @theCount As int
Select @theCount = Count(Class_Id) From Class Where Class_Id=@Class_Id or Class_Path like ''+@oClass_Path+'|%'
IF @theCount Is Null
Begin
Set @Err = 3
Goto theEnd
End
IF @Parent_ID=0 --如果是设置为顶级节点，将节点设置为最后一个顶级节点
Begin
--Print '设置为顶级栏目'
Set @nClass_Path = Ltrim(Str(@Class_Id))
Set @nClass_Depth =1
Select @nClass_Order = Max(Class_Order) From Class
IF @nClass_Order Is NULL
Begin
Set @Err = 4
Goto theEnd
End
Set @nClass_Order = @nClass_Order - @theCount + 1
--更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序
--Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的：Class_Order'
Update Class Set Class_Order = Class_Order-@theCount Where (Class_Order >@oClass_Order) And (Class_Path Not like ''+@oClass_Path+'|%')
IF @@Error <> 0
Begin
Set @Err = 7
Goto theEnd
End
--Print '更新本栏目的：Parent_ID,Class_Path,Class_Depth,Class_Order,Class_Name,Class_Intro'
Print 'Order : '+Ltrim(Str(@nClass_Order))
Update Class Set Parent_ID=@Parent_ID,Class_Path = @nClass_Path,Class_Depth = @nClass_Depth,Class_Order = @nClass_Order, Class_Name = @Class_Name,Class_Intro = @Class_Intro Where Class_Id = @Class_Id
IF @@Error <> 0
Begin
Set @Err = 5
Goto theEnd
End
--Print '更新本栏目下的所有子栏目的：Class_Path,Class_Depth,Class_Order'
Update Class Set Class_Path = Replace(Class_Path,@oClass_Path,@nClass_Path),Class_Depth = Class_Depth + (@nClass_Depth-@oClass_Depth),Class_Order = Class_Order+( @nClass_Order-@oClass_Order) Where Class_Path like ''+@oClass_Path+'|%'
IF @@Error <> 0
Begin
Set @Err = 6
Goto theEnd
End
End
Else
Begin
--获取未来父节点的相关信息,并设置本节点的相关值
Select @nClass_Depth = Class_Depth,@nClass_Path = Class_Path From Class Where Class_Id = @Parent_ID
IF @nClass_Depth Is NULL or @nClass_Path Is Null
Begin
Set @Err = 8
Goto theEnd
End
Set @nClass_Depth = @nClass_Depth +1
Select @nClass_Order =Max(Class_Order) From Class Where Class_Id = @Parent_ID or Class_Path like ''+@nClass_Path+'|%'
IF @nClass_Order Is NULL
Begin
Set @Err = 9
Goto theEnd
End
Set @nClass_Path = @nClass_Path +'|'+ Ltrim(Str(@Class_Id))
IF @nClass_Order = @oClass_Order+1 --如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变
Begin
Update Class Set Parent_ID=@Parent_ID,Class_Path = @nClass_Path,Class_Depth = @nClass_Depth, Class_Name = @Class_Name,Class_Intro = @Class_Intro Where Class_Id = @Class_Id
IF @@Error <> 0
Begin
Set @Err = 10
Goto theEnd
End
End
Set @nClass_Order = @nClass_Order + 1
--更新三部分 1 本树更改之前的后面（或前面）记录的顺序 1 节点本身 3 所有子节点
--分为向上移或象下移
--Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置] [不包括本栏目下的子栏目]的：Class_Order'
IF @nClass_Order < @oClass_Order
Begin
Update Class Set Class_Order = Class_Order+@theCount Where Class_Order<@oClass_Order And Class_Order >=@nClass_Order And (Class_Path Not like ''+@oClass_Path+'|%') And Class_Id<>@Class_Id
IF @@Error <> 0
Begin
Set @Err = 12
Goto theEnd
End
End
Else
Begin
Update Class Set Class_Order = Class_Order-@theCount Where Class_Order >@oClass_Order And Class_Order<@nClass_Order And (Class_Path Not like ''+@oClass_Path+'|%') And Class_Id<>@Class_Id
IF @@Error <> 0
Begin
Set @Err = 13
Goto theEnd
End
End
--Print '更新本栏目的：Parent_ID,Class_Path,Class_Depth,Class_Order,Class_Name,Class_Intro'
Print 'Order : '+Ltrim(Str(@nClass_Order))
IF @nClass_Order > @oClass_Order
Set @nClass_Order = @nClass_Order - @theCount
Update Class Set Parent_ID=@Parent_ID,Class_Path = @nClass_Path,Class_Depth = @nClass_Depth,Class_Order = @nClass_Order, Class_Name = @Class_Name,Class_Intro = @Class_Intro Where Class_Id = @Class_Id
IF @@Error <> 0
Begin
Set @Err = 10
Goto theEnd
End
--Print '更新本栏目下的所有子栏目的：Class_Paht,Class_Depth,Class_Order'
Update Class Set Class_Path = Replace(Class_Path,@oClass_Path,@nClass_Path),Class_Depth = Class_Depth + (@nClass_Depth-@oClass_Depth),Class_Order = Class_Order+(@nClass_Order-@oClass_Order) Where Class_Path like ''+@oClass_Path+'|%'
IF @@Error <> 0
Begin
Set @Err = 11
Goto theEnd
End
End
theEnd:
IF @Err<>0 --如果有错误则返回错误号
Begin
Rollback Tran
Return @Err
End
Else --如果没有错误就返回0
Begin
Commit Tran
Return 0
End

 

 

 

查询：

Create PROCEDURE sp_Class_List
AS
Select Class_Id, Class_Name, Parent_ID, Class_Path, Class_Depth,
Class_Order, Class_Intro
FROM Class
orDER BY Class_Order
GO

 

 

 

调用：

exec sp_Class_Insert 4,"家具4","jiaju4" --parent_id,,,
exec sp_Class_List
exec sp_Class_Delete 2
exec sp_Class_Update 4,2, "家具a","jiajua" --class_id parent_id name ..
select * from Class
delete class from class where class_name like '%家具%'

所有父类
declare @sql varchar(max)
select  @sql= ' SELECT CategoryId,CategoryName,CategoryPath   FROM B_Category 
  WHERE CategoryId IN ('+categorypath+')'  from B_Category  where CategoryId=53
exec (@sql) 
--in(51,53)