本文和大家分享简单实用SQL脚本Part：生成站点导航树形结构，挺不错的教程哦。
一、需求
我们通常的时候，都是先有站点地图，之后对URL的地址进行判断和导航的，那么我们是否可以根据所有收集到的URL来进行分解，从而生成出所有可能的地址，最主要的是要有一个层级的关系。
我们的数据一般都是如下图1所示的格式，那么我们如何在页面上展现出图2的效果呢？
（图1：原始表）
（图2：页面效果图）
其实就普通树形来说，ID与ParentID这两个字段是少不了的。
（图3：数据效果图）
二、逻辑分析
- 使用字符串分割法把一条记录的pagepath字段进行分解成我们需要的字符串，但是这里的分割有一点点不一样，那就是我们需要把前面的字符串也一起返回；
- 之后我们就可以对这一段段的字符串进行逻辑处理，也就是进行树形结构的组建；
a) 我们先来判断@Temppath这新分割出来的字符串是否在表PageUrlTree存在；
b) 如果是第一条数据，那就是根目录：'/'这个时候就需要特殊处理，把它当成一个根节点，手动设置ID和ParentID值；
c) 如果不是第一条数据，那就找出上一个pagepath值来查找出ID值，再进行整型数值的运算来递增一个新值出来作为新纪录的ID值；
d) @countBefore与@TempPath_Before的设定很有意义，因为这是对ParentID判断的一个变量；
三、技术点
- 数据库结构树的设计逻辑；
- 数据库游标的使用；
- SQL字符串的分割；
- SQL判断一个字符在字符串中出现的次数；
- 字符串与整数类型之间的转换；
- 保存了上个Parent的PagePath值；
- 当没有返回值的时候，可以进行@MaxID is null这样的判断
四、代码实现
步骤1：首先创建一个测试表，这个测试表只包括了pagepath这个字段，等下就会对这个字段进行分析，分解成不同的记录和不同的属性值。
--1,创建测试表 if exists (select * from sysobjects where id = OBJECT_ID('[temp_url]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [temp_url] CREATE TABLE [temp_url] ( [pagepath] [varchar] (300) NULL) INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/easyurl/pages/index.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/easyurl/pages/reportContent.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/easyurl/pages/sort_edit.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/performancemonitor/pages/OnlinePerformBySystem.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/performancemonitor/pages/PerformanceContent.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/siteMapManage/siteMapManageEdit.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/warning/pages/ErrorWarning.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/apps/warning/pages/ErrorWarningByPage.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/msg/pages/Error.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/msg/pages/LogQuery.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/msg/pages/success.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/ChangePassword.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/CustomerUserRoles.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/editresource.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/EditRole.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/OnlineUserRolesManager.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/resources.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/Roles.aspx') INSERT [temp_url] ([pagepath]) VALUES ( '/Web/contrib/security/pages/Users.aspx')
步骤2：接着创建一个用来保存这个树形的结构表，[ID]和[ParentID]是少不了的了，[PagePath]是页面路径的字符串，[IsPage]表示这条记录是否是页面，也就是说是否是叶子节点。
-- 2,创建结果表 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [ dbo ] . [ PageUrlTree ] ( [ ID ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL , [ ParentID ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL , [ PagePath ] [ varchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL , [ IsPage ] [ int ] NULL CONSTRAINT [ DF_PageUrlTree_IsPage ] DEFAULT (( 0 )) ) ON [ PRIMARY ] GO SET ANSI_PADDING OFF
步骤3：接着创建一个用户函数，[Get_StrArrayStrOfIndex]这个函数是返回一个字符串中第n个分隔符之前的所有字符串，例如：/Web/apps/pages/index.aspx，当执行select [dbo].[Get_StrArrayStrOfIndex]('/Web/apps/pages/index.aspx','/','4'),返回值就是：“/Web/apps/pages/”，这样做就循环对字符串进行处理了。
Code
[http://www.xueit.com]
-- 3,创建处理函数 -- ============================================= -- Author: <Viajar> -- Create date: <2010.09.01> -- Description: <获取字符串的第n个分隔字符串> -- ============================================= Create function [ dbo ] . [ Get_StrArrayStrOfIndex ] ( @str varchar ( 50 ), -- 要分割的字符串 @split varchar ( 10 ), -- 分隔符号 @index int -- 取第几个元素 ) returns varchar ( 50 ) as begin declare @location int declare @start int declare @next int declare @seed int set @str = ltrim ( rtrim ( @str )) set @start = 1 set @next = 1 set @seed = len ( @split ) set @location = charindex ( @split , @str ) while @location <> 0 and @index > @next begin set @start = @location @seed set @location = charindex ( @split , @str , @start ) set @next = @next 1 end if @location = 0 select @location = len ( @str ) 1 return substring ( @str , 0 , @location 1 ) end步骤4：接着创建一个存储过程，[sp_CreatePagePathTree]这个存储过程是对这个方案的整个逻辑进行处理。
-- 4,处理的存储过程 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Viajar> -- Create date: <2010.09.15> -- Description: <创建站点树形结构> -- Tips: <读取PageURL表，生成数据到PageUrlTree表> -- ============================================= CREATE PROCEDURE sp_CreatePagePathTree AS BEGIN -- 清空记录 truncate table PageUrlTree -- 生成树 DECLARE @TempPath_Before varchar ( 100 ) DECLARE @TempPath varchar ( 100 ) DECLARE @pagepath varchar ( 50 ) DECLARE @itemCur CURSOR SET @itemCur = CURSOR FOR SELECT pagepath FROM temp_url OPEN @itemCur FETCH NEXT FROM @itemCur INTO @pagepath WHILE @@FETCH_STATUS = 0 BEGIN -- 逻辑处理 DECLARE @Word NVARCHAR ( 2 ) DECLARE @WordAll NVARCHAR ( 50 ) DECLARE @count int DECLARE @countBefore int DECLARE @i int DECLARE @ID VARCHAR ( 50 ) DECLARE @ParentID VARCHAR ( 50 ) DECLARE @MaxID VARCHAR ( 50 ) DECLARE @SubID int DECLARE @IsPage int set @IsPage = 0 set @i = 1 set @Word = ' / ' set @WordAll = @pagepath select @countBefore = len ( replace ( @WordAll , @Word , @Word ' _ ' )) - len ( @WordAll ) 1 select @count = len ( replace ( @WordAll , @Word , @Word ' _ ' )) - len ( @WordAll ) 1 select @TempPath_Before = dbo. [ Get_StrArrayStrOfIndex ] ( @pagepath , ' / ' , 1 ) WHILE @count > 0 begin select @Temppath = dbo. [ Get_StrArrayStrOfIndex ] ( @pagepath , ' / ' , @i ) print ' @Temppath: ' @Temppath -- 不存在 if not exists ( select ID from PageUrlTree where PagePath = @Temppath ) begin select @ParentID = ID from PageUrlTree where PagePath = @TempPath_Before print ' @ParentID: ' @ParentID if ( @ParentID <> '' ) begin select @MaxID = max (ID) from PageUrlTree where ParentID = @ParentID and PagePath like @TempPath_Before ' % ' if ( @MaxID is null ) set @SubID = 100 else select @SubID = convert ( int , substring ( @MaxID , len ( @MaxID ) - 2 , len ( @MaxID ))) 1 -- 如果是.的话就表示是页面 if ( charindex ( ' . ' , @TempPath ) > 0 ) set @IsPage = 1 INSERT PageUrlTree VALUES ( @ParentID convert ( varchar ( 10 ), @SubID ), @ParentID , @Temppath , @IsPage ) end else INSERT PageUrlTree VALUES ( ' 100 ' , ' 0 ' , @Temppath , @IsPage ) -- 根节点 end set @i = @i 1 set @countBefore = @count set @count = @count - 1 set @TempPath_Before = @TempPath end FETCH NEXT FROM @itemCur INTO @pagepath END CLOSE @itemCur DEALLOCATE @itemCur END GO
五、总结
其中这个完全可以使用程序代码来解决，但是既然用了SQL，那就把这个过程记录下来吧，希望对其它人有帮助吧。
扫一扫
1470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
