创建：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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'
|
增加：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
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
|
删除：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
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
|
更新：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
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
|
查询：
|
1
2
3
4
5
6
7
|
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
|
调用：
|
1
2
3
4
5
6
|
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
'%家具%'
|
|
1
|
<br>
|
所有父类
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)
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)
