使用SQL语句生成站点导航树形结构的实例
来源:互联网 发布:淘宝客服的连接地址 编辑:程序博客网 时间:2024/06/05 07:14
本文和大家分享简单实用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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET 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/”,这样做就循环对字符串进行处理了。
--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)asbegin 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Viajar>-- Create date: <2010.09.15>-- Description: <创建站点树形结构>-- Tips: <读取PageURL表,生成数据到PageUrlTree表>-- =============================================CREATE PROCEDURE sp_CreatePagePathTree ASBEGIN --清空记录 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 @itemCurENDGO
五、总结
其中这个完全可以使用程序代码来解决,但是既然用了SQL,那就把这个过程记录下来吧,希望对其它人有帮助吧。
- 使用SQL语句生成站点导航树形结构的实例
- 单实用SQL脚本Part:生成站点导航树形结构
- 树形结构的sql语句
- Oracle树形结构的sql语句
- Oracle树形结构的sql语句
- 单表查询树形结构的SQL语句
- sql 树形结构查询语句 (sql 2005+)
- SQL SERVER 生成表结构的语句
- MYSQL使用SQL语句生成表结构文档语句
- ios-使用树形结构导航模式
- 树形结构的使用
- EasyUI使用tree生成树形结构加载两次的问题
- SQL语句生成表结构
- 动态生成sql语句实例
- 如何用SQL查询语句获取Oracle表 树形结构的记录(PL/SQL )
- 使用 ASP.NET 2.0 生成 Web 站点以导航到您的音乐库
- 使用 ASP.NET 2.0 生成 Web 站点以导航到您的音乐库
- 使用 ASP.NET 2.0 生成 Web 站点以导航到您的音乐库
- 操作数据库时,一个方法的多个异常
- doxygen用法整理
- RGB565、RGB555、RGB888图像DIB显示
- 逗号操作的详谈
- B/S,C/S架构混合使用
- 使用SQL语句生成站点导航树形结构的实例
- VC中判断文件夹是否存在的方法(C/C++)
- 光脚丫学LINQ(031):开篇及一对多映射关系的再学习
- JDBC批量执行sql(转)
- Firebird 数据库使用经验总结
- Java中volatile关键字的效果
- RUP开发实践
- Subversion 错误信息一览表
- XP 极限编程