其思路是:生成分类的代及代序列,以代形成缩进并以代序列排序。
以下是返回以代序列排序的分类列表。以代形成缩进略。
1
2 -- =============================================
3 -- Author: <Author,,Name>
4 -- Create date: <Create Date,,>
5 -- Description: <Description,,>
6 -- =============================================
7 ALTER PROCEDURE [ dbo ] . [ CategoryTreeGeneration ]
8 -- Add the parameters for the stored procedure here
9 AS
10 BEGIN
11 -- SET NOCOUNT ON added to prevent extra result sets from
12 -- interfering with SELECT statements.
13 Set NOCOUNT ON
14
15 Create Table #CategoryTree(
16 CategoryID Nvarchar ( 10 ),
17 Generation int ,
18 GenerationLine Varchar ( 25 ) Default ''
19 )
//构建临时表,存储每一分类的ID,代,代序列
20
21 Declare
22 @Generation int ,
23 @Root Nvarchar ( 10 )
24
25 set @Generation = 1
26 set @Root = ' 0 '
27 //根的分类ID
28 insert #CategoryTree (CategoryID, Generation,GenerationLine)
29 select @Root , @Generation , @Root
//填充根分类
30 WHile @@RowCount > 0
31 Begin
32 set @Generation = @Generation + 1
33 insert #CategoryTree(CategoryID,Generation,GenerationLine)
34 SELECT ArticleCategory.CategoryID, @Generation ,#CategoryTree.GenerationLine
35 + ' ' + substring (ArticleCategory.CategoryID, len (ArticleCategory.CategoryID) - 1 , 2 )
//分类ID以两位分级,所以取ID后两位作为代序列取值
36 from ArticleCategory
37 join #CategoryTree
38 on #CategoryTree.Generation = @Generation - 1
39 And
40 ArticleCategory.ParentID = #CategoryTree.CategoryID
41 end
42
43 select ID,#CategoryTree.CategoryID as CategoryID,Generation, CategoryName from #CategoryTree join ArticleCategory
44 on #CategoryTree.CategoryID = ArticleCategory.CategoryID
45 order by GenerationLine
//以代序列排序输出
46
47 Set NOCOUNT Off
48
49
50
51 END
52
53
54
55
56
57
2 -- =============================================
3 -- Author: <Author,,Name>
4 -- Create date: <Create Date,,>
5 -- Description: <Description,,>
6 -- =============================================
7 ALTER PROCEDURE [ dbo ] . [ CategoryTreeGeneration ]
8 -- Add the parameters for the stored procedure here
9 AS
10 BEGIN
11 -- SET NOCOUNT ON added to prevent extra result sets from
12 -- interfering with SELECT statements.
13 Set NOCOUNT ON
14
15 Create Table #CategoryTree(
16 CategoryID Nvarchar ( 10 ),
17 Generation int ,
18 GenerationLine Varchar ( 25 ) Default ''
19 )
//构建临时表,存储每一分类的ID,代,代序列
20
21 Declare
22 @Generation int ,
23 @Root Nvarchar ( 10 )
24
25 set @Generation = 1
26 set @Root = ' 0 '
27 //根的分类ID
28 insert #CategoryTree (CategoryID, Generation,GenerationLine)
29 select @Root , @Generation , @Root
//填充根分类
30 WHile @@RowCount > 0
31 Begin
32 set @Generation = @Generation + 1
33 insert #CategoryTree(CategoryID,Generation,GenerationLine)
34 SELECT ArticleCategory.CategoryID, @Generation ,#CategoryTree.GenerationLine
35 + ' ' + substring (ArticleCategory.CategoryID, len (ArticleCategory.CategoryID) - 1 , 2 )
//分类ID以两位分级,所以取ID后两位作为代序列取值
36 from ArticleCategory
37 join #CategoryTree
38 on #CategoryTree.Generation = @Generation - 1
39 And
40 ArticleCategory.ParentID = #CategoryTree.CategoryID
41 end
42
43 select ID,#CategoryTree.CategoryID as CategoryID,Generation, CategoryName from #CategoryTree join ArticleCategory
44 on #CategoryTree.CategoryID = ArticleCategory.CategoryID
45 order by GenerationLine
//以代序列排序输出
46
47 Set NOCOUNT Off
48
49
50
51 END
52
53
54
55
56
57