建立一个jquery实现树形结构构的SQL表 3实现的功能

点击联系发帖人 时间：2014-11-26 14:31

sql 树形结构

第十一届“挑战杯”国赛获奖作品——基于SQL语法树形结构分析的服务器防护系统
项目名称：基于SQL语法树形结构分析的服务器防护系统
人人移动客户端下载9.14  组织图─树型结构展开_第9章 SQL用法进阶_华初网
所在位置：
第9章 SQL用法进阶<font color="#.14  组织图─树型结构展开
9-9& (1) 自连接()()()()(Join)(Self-Join)(2) 递归运算()()(Recursive)()Oracle9i()CONNECT BYSQL Server2005()WITHSQL Server 2005()Oracle11g R2SQL Server 2000SQL Server 2000SQL Server 2000(Temp Table)SQLSQLCREATE PROCEDURE dbo.GetDeptTree(&& @Dept_id&& varchar(10))ASBEGIN& -- 1. 建立暂存表(hold the parent/children-items)& CREATE TABLE #Relation& (&&& Dept_id&&&&&&&& varchar (10),&&&&&&& & -- 部门代码&&& Dept_Name&&& &&&&&& nvarchar (200),& &&&&&&&&&&&&&&& -- 部门名称&&& Dept_Level&&&&& int ,&&&&&&&&&&& &&&&&&&&&&&&&&&&& -- 部门层级(非必要)&&& Upper_Dept_id&& varchar(10),&&&& &&&&&&&& -- 上层(父)部门代码&&& Runs&&&&&&&&&&& int&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&& -- 记录是在第几次执行时，存入暂存数据表&& )&& -- 2. 建立锚点(Anchor)& -- 将输入的部门存入暂存数据表& -- 因输入的部门为本次展开的最上层的部门，因此将其上层部门的代码设为NULL& -- Runs设为0，表示初始化条件& INSERT INTO #Relation&&&&& ( Dept_id, Dept_Name, Dept_Level, Upper_Dept_id, Runs)& SELECT D.Dept_id, D.Dept_Name, D.Dept_Level, NULL, 0& FROM Departments D& WHERE 1=1&&&&& AND Dept_id = @Dept_id --'A000'&& -- 3. 逐层展开(Loop as long as we find uninserted children)& WHILE @@ROWCOUNT & 0 & -- 3.3 重复执行，直至无任何数据被存入& BEGIN&&&&& -- Insert children that are not already in the temporary table&&&&& INSERT INTO #Relation&&&&&&&&&& (Dept_id, Dept_Name, Dept_Level, Upper_Dept_id, Runs)&&&&&& SELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id, T.Runs +1&&&&&& FROM #Relation R RIGHT JOIN -- 3.2和3.1注释部分所取得的下属部门，删除已位于暂存数据表中的值&&&&&&&& (&&&&&&&& -- 3.1 找出暂存数据表中所有的下属部门，当执行到第二次时会有重复值&&&&&&&& SELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id, P.Runs&&&&&&&& FROM Departments T, #Relation P &&&&&&&& WHERE 1=1&&&&&&&&&&& AND P.Dept_id = T.Upper_Dept_id&&&&&&&& ) T&&&&&&&& ON R.Dept_id = T.Dept_id&&&&& WHERE 1=1&&&&&&&& AND R.Dept_id IS NULL&& END&& -- 4. 传回暂存数据表& SELECT P.Dept_id, P.Dept_Level, P.Upper_Dept_id, P.Dept_Name& FROM #Relation P& WHERE 1=1& ORDER BY P.Dept_id, P.Dept_Level&& -- 删除暂存数据表 (非必要,但建议执行)& DROP TABLE #RelationENDGO(A000)(Runs)&SQL(A000)(A000)(Anchor)()()-- 3. 逐层展开(Loop as long as we find uninserted children)WHILE @@ROWCOUNT & 0 -- 3.3重复执行，直至无任何数据被存入BEGIN&&& -- Insert children that are not already in the temporary table&&&&&& INSERT INTO #Relation&&&&&& (Dept_id, Dept_Name, Dept_Level, Upper_Dept_id, Runs)&&&&&& SELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id, T.Runs +1&&&&&& FROM #Relation R RIGHT JOIN -- 3.2和3.1所取得的下属部门，删除已存在于暂存数据表中的值&&&&&& (&&&&&& -- 3.1找出暂存数据表中所有的下属部门，当执行到第二次时会有重复值&&&&&& SELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id, P.Runs&&&&&& FROM Departments T, #Relation P &&&&&& WHERE 1=1&&&&&&&&&& AND P.Dept_id = T.Upper_Dept_id&&&&&& ) T&&&&&& ON R.Dept_id = T.Dept_id&&& WHERE 1=1&&&&&& AND R.Dept_id IS NULLENDStep01()(INNER JOIN)(A000)F000I000R000W000 44Runs145(1+4)()(A000)4(F000I000R000W000)Step02(Subtract)SQL Server 2005()EXCEPTSQL Server 2000(OUTER JOIN)SQLNULLNULLNULLRuns1(A000)4(Subtract)NULLNULLNULLSELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id, T.Runs +1&&&&& , R.Dept_id --为清楚说明而特别增加的字段，即WHERE条件筛选子句FROM #Relation R RIGHT JOIN &&&&& (&&&&& SELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id, P.Runs&&&&& FROM Departments T, #Relation P &&&&& WHERE 1=1&&&&&&&&&&& AND P.Dept_id& T.Upper_Dept_id&&&&& ) TON R.Dept_id = T.Dept_idWHERE 1=1&&&&& --AND R.Dept_id IS NULL--为NULL值则表示是新增的部门，为说明概念暂时不使用SQL&Step03SQL Server@@ROWCOUNT00SQL ServerSELECTEXEC GetDeptTree 'A000'SQL Server 2000SQLSQL Server 2005()WITHSQL Server 2005(含)/ Oracle 11g R2(含)以上版本SQL Server 2005()(WITH)Oracle9i11g R2()13.2CTE9.10WITH Relation(Dept_id,Dept_Name,Dept_Level,Upper_Dept_id,Rel_Lvl,Dept_List) AS(& --1. 锚点区(Anchor)& SELECT D.Dept_id, D.Dept_Name, D.Dept_Level&&&&&&& &, CAST(NULL AS VARCHAR(10)) Upper_Dept_id C关键点, 类型需与递归调用区的相同&&&&&&&& , 0 Rel_Lvl&&&&&&&& , CAST(Dept_id AS VARCHAR(2000)) Dept_List& FROM Departments D& WHERE 1=1&&&&&&& AND Dept_id = 'A000'& UNION ALL―关键点, 必须采用UNION ALL结合两区& --2. 递归调用区& SELECT T.Dept_id, T.Dept_Name, T.Dept_Level, T.Upper_Dept_id&&&&&&&&&& , Rel_Lvl +1 Rel_Lvl&&&&&&&&&& , CAST((Dept_List + '\' + T.Dept_id ) AS VARCHAR(2000)) Dept_List& FROM Departments T, Relation& P& WHERE 1=1&&&&&&& AND P.Dept_id = T.Upper_Dept_id)SELECT Dept_id, Dept_Name, Dept_Level, Upper_Dept_id, Rel_Lvl, Dept_ListFROM Relation关键点一：递归起点－锚点区A000NULLNULLCAST(NULL AS VARCHAR(10))关键点二：递归调用(CTERelation)(Departments)关键点三：递归终止3SQL Server 2000 Oracle 9i(含)以上版本Oracle9i()(Hierarchical Queries)CONNECT BY()CONNECT BYSQLSELECT Dept_Id&&&&& , Dept_Name&&&&& , Upper_Dept_Id&&& Upper_Dept&&&&& , Level - 1&&&&&&& Rel_LVL&&&&& , SYS_CONNECT_BY_PATH(Dept_Id, '\') Dept_Id_Path&&&&& --, SYS_CONNECT_BY_PATH(Dept_Name, '-') Dept_Name_PathFROM DepartmentsSTART WITH dept_id = 'A000' --upper_dept_id IS NULLCONNECT BY PRIOR Dept_ID = Upper_Dept_Id --前后顺序将影响递推方向，与下一个范例正好相反&&CONNECT BYCONNECT BYPRIORSTART WITH(Row)LEVEL(Pseudocolumns)(A000)START WITH(A000)CONNECT BY PRIORDept_ID = Upper_Dept_Id WHERE()(Node)(Brach)SQLWHERECONNECT BYWHEREWHEREWHERECONNECT BYI200SQLWHERESELECT Dept_Id&&&&& , Dept_Name&&&&& , Upper_Dept_IdFROM DepartmentsWHERE 1=1&&&&& AND Dept_Id && 'I000'START WITH Upper_Dept_id IS NULLCONNECT BY PRIOR Dept_ID = Upper_Dept_IdCONNECT BYSELECT Dept_Id&&&&&& , Dept_Name&&&&&& , Upper_Dept_IdFROM DepartmentsSTART WITH Upper_Dept_id IS NULLCONNECT BY Dept_Id && 'I000' &&&&&&& AND PRIOR Dept_ID = Upper_Dept_Id条件设定在WHERE子句中的执行结果条件设定在CONNECT BY命令中的执行结果(订单日期转置数据表)&&&&I000I100I200WHEREI000CONNECT BYI000WHERECONNECT BY13.1Oracle CONNECT BYOracleCONNECT BYWITHSQL Server 2005SELECT Dept_Id&&&&& , Dept_Name&&&&& , Upper_Dept_Id&&&& Upper_Dept&&&&& , Level - 1&&&&&&&& Rel_LVL&&&&& , SYS_CONNECT_BY_PATH(Dept_Id, ' \ ') Dept_Id_Path&&&&& , SYS_CONNECT_BY_PATH(Dept_Name, ' ') Dept_Name_PathFROM DepartmentsSTART WITH dept_id = 'I200'CONNECT BY PRIOR Upper_Dept_Id =Dept_ID--前后顺序将影响递推方向，与前一个范例正好相反&CONNECT BY PRIOR()(I200)WITHSQL Server 2005WITH Relation(Emp_Id,Emp_Name,Dept_id,Dept_Name,Upper_Dept_id,Rel_Lvl, Ctrl_List) AS (SELECT E.Emp_Id&&&&& , E.Emp_Name&&&&& , D.Dept_Id&&&&& , D.Dept_Name&&&&& , CASE WHEN E.Emp_Id = D.Mgr_Id THEN D.Upper_Dept_id &&& &&&&&&&&ELSE D.Dept_Id &&&&&&& END Upper_Dept_id --判断是否为该部门的主管，若不是，则需处理所属部门的主管&&&&& , 0 Rel_Lvl&&&&& , CAST(E.Emp_Name& AS VARCHAR(2000)) Ctrl_List&&&&& --, CAST(E.Emp_Name + ' (' + E.Emp_Id +')' AS VARCHAR(2000)) Ctrl_ListFROM Employees E, Departments DWHERE 1=1&&&& AND E.Dept_Id = D.Dept_Id&&&& AND E.Emp_Id = '; UNION ALLSELECT E.Emp_Id&&&&& , E.Emp_Name&&&&& , D.Dept_id&&&&& , D.Dept_Name&&&&& , D.Upper_Dept_id&&&&& , P.Rel_Lvl - 1 Rel_Lvl&&&&& , CAST((E.Emp_Name + '\' + P.Ctrl_List ) AS VARCHAR(2000)) Ctrl_List&&&&& --, CAST((E.Emp_Name + ' (' + E.Emp_Id +')'&& + ' \ ' + P.Ctrl_List ) AS VARCHAR(2000)) Ctrl_ListFROM Employees E, Departments D, Relation PWHERE 1=1&&&&& AND E.Emp_Id = D.Mgr_Id&&&&& AND P.Upper_Dept_id = D.Dept_id)SELECT Emp_Id, Emp_Name, Dept_id, Dept_Name, Upper_Dept_id, Rel_Lvl, Ctrl_ListFROM Relation&※& Oracle 11g R2使用者只需将字符串连接符号修改为“||”即可。&Jack(920075)WITHJack(920075)()()SQL Server 2000OracleCONNECT BYSQL Server 2005Oracle 11g R2WITH()&提示SQLSQL&
－－－－－在mysql中建立树形结构
在中建立树形结构
这个不是什么新东西了，网上有很多方法，我也尝试了其中好的方法，都不尽人意，这是我最后我推荐的方法，共享一下，大家一起讨论，当然难如高手法眼，呵呵。我就直接贴代码了，有问题就联系我。
mysql中要有树形结构我认为表中应该有如写字段：
举例菜单表menu:
create table menu &
& &id & & & & & & & & & int not null auto_increment, &
& &pid & & & & & & & & &int, &
& &name & & & & & & & & varchar(100) not null, &
& &nlevel & & & & & & & int, &
& &scort & & & & & & & &varchar(8000), &
& &primary key (id) &
type = InnoDB; &
alter table menu add constraint FK_Reference_67 foreign key (pid) &
& & & references menu (id) on delete restrict on update restrict &
&创建存储过程genNode_menu：
&DECLARE L &
&Set Level=0 ; &
&update menu a inner join (SELECT id,Level,concat(',',ID,',') scort FROM menu WHERE pid is null) b on a.id=b.id & & &
&set a.nlevel=b.level,a.scort=b. &
&WHILE FOUND_ROWS()&0 DO &
& SET Level=Level+1; &
update menu a inner join ( &
& &SELECT ID,Level,scort FROM menu &&
& & WHERE nLevel=Level-1) b on a.pid=b.id &
&set a.nlevel=b.level,a.scort=concat(b.sCort,a.ID,','); &
&END WHILE; &
插入数据：
INSERT INTO menu VALUES ('1', null, '菜单1', null, null); &
INSERT INTO menu VALUES ('2', '1', '菜单1-1', null, null); &
INSERT INTO menu VALUES ('3', null, '菜单2', null, null); &
INSERT INTO menu VALUES ('4', '3', '菜单2-1', null, null); &
INSERT INTO menu VALUES ('5', '4', '菜单2-1-1', null, null); &
执行存储过程：
call genNode_ &
我们看一看menu表现在是什么情况了：
很好，就是这个效果
现在可以按你的需求随便查询了：
select * from menu a where a.scort not like '%,1,%' order by a.scort &
作者 lifaming15建立一个树形结构的SQL表 3实现的功能_百度知道
提问者采纳
主要是要有ID，PID两个字段，下面是我用的一个表，仅供参考：CREATE TABLE [dbo].[Sys_Menu]( [ID] [int] NOT NULL, [Code] [varchar](50) NOT NULL, [PID] [int] NOT NULL, [Name] [nvarchar](50) NULL, [Url] [varchar](100) NULL, [STATE] [bit] NOT NULL, [IsSelected] [bit] NULL) ON [PRIMARY]GO
有好多不明白
有的字符不认识
我们才学到内连接
这里面没有内连接，那些不认识，这些你的书都应该有的，这是最基本的
提问者评价
谢谢我查查能不能帮我把表建一下你的表我有的看不懂才学不久
其他类似问题
为您推荐：
树形结构的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁}

叫阿莫西中心