無限級分類 父節點 子節點

​​

​​

建立：

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)