id name pid
1 test1 0
2 test2 1
3 test3 1
4 test4 2
5 test5 3
6 test6 5
写sql加一列level计算出每个节点的层级。
我是实在写不出来。
------解决方案--------------------
- SQL code
-- 使用函数的方法：--建立 演示环境if object_id('tb_bookInfo') is not null drop table tb_bookInfogocreate table tb_bookInfo(number int,name varchar(10),type int)insert tb_bookInfoselect 1 ,'n1', 6 union allselect 2 ,'n2', 3if object_id('tb_bookType') is not null drop table tb_bookTypegocreate table tb_bookType(id int,typeName varchar(10),parentid int)insert tb_bookTypeselect 1,'英语',0 union allselect 2,'生物',0 union allselect 3,'计算机',0 union allselect 4,'口语',1 union allselect 5,'听力',1 union allselect 6,'数据库',3 union allselect 7,'软件工程',3 union allselect 8,'SQL Server',6select a.*,b.level from tb_bookInfo a,f_getC(3) b where a.type=b.id order by b.level /*number name type level ----------- ---------- ----------- -----------2 n2 3 01 n1 6 1（所影响的行数为 2 行）*/--查所有父结点if object_id('f_getP') is not null drop function f_getPgocreate function f_getP(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @[email protected]+1 insert @re select a.parentid,@l from tb_bookType a,@re b where a.id=b.id and [email protected] and a.parentid<>0 end update @re set [email protected] return end go --查所有子结点if object_id('f_getC') is not null drop function f_getCgocreate function f_getC(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @[email protected]+1 insert @re select a.id,@l from tb_bookType as a,@re as b where b.id=a.parentid and [email protected] end return end go --查所有父子结点if object_id('f_getAll') is not null drop function f_getAllgocreate function f_getAll(@id int) returns @re table(id int,level int) as begin declare @l int set @l=0 insert @re select @id,@l while @@rowcount>0 begin set @[email protected]+1 insert @re select a.parentid,@l from tb_bookType a,@re b where a.id=b.id and [email protected] and a.parentid<>0 end update @re set [email protected] while @@rowcount>0 begin set @[email protected]+1 insert @re select a.id,@l from tb_bookType as a,@re as b where b.id=a.parentid and [email protected] end returnend go --删除演示drop table tb_bookInfodrop table tb_bookTypedrop function f_getPdrop function f_getCdrop function f_getAllGO--sqlserver2005的新方法-- 建立演示环境IF OBJECT_ID('[Dept]') IS NOT NULL DROP TABLE [Dept]GOCREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO--1、父-〉子-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id)SELECT * FROM DEPTSGO--结果如下/*id parent_id name ----------- ----------- --------------------6 4 MIS7 6 UI8 6 软件开发9 8 内部开发（所影响的行数为 4 行）*/--2、子-〉父-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'内部开发';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent FROM Dept where @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id)SELECT * FROM DEPTSGO--结果如下/*id parent_id name ----------- ----------- --------------------9 8 内部开发8 6 软件开发6 4 MIS4 0 业务部（所影响的行数为 4 行）*/-- 删除演示环境DROP TABLE Dept