计算树表的level

[a291410855]

id name pid
1 test1 0
2 test2 1
3 test3 1
4 test4 2
5 test5 3
6 test6 5


写sql加一列level计算出每个节点的层级。



我是实在写不出来。

[AcHerat]

-- 使用函数的方法：

--建立 演示环境

if object_id('tb_bookInfo') is not null drop table tb_bookInfo
go
create table tb_bookInfo(number int,name varchar(10),type int)
insert tb_bookInfo
select 1 ,'n1', 6 union all
select 2 ,'n2', 3


if object_id('tb_bookType') is not null drop table tb_bookType
go
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英语',0 union all
select 2,'生物',0 union all
select 3,'计算机',0 union all
select 4,'口语',1 union all
select 5,'听力',1 union all
select 6,'数据库',3 union all
select 7,'软件工程',3 union all
select 8,'SQL Server',6

select 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           0
1           n1         6           1

（所影响的行数为 2 行）
*/
--查所有父结点
if object_id('f_getP') is not null drop function f_getP
go
create 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 @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level
    return 
end 
go 


--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create 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 @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return 
end 
go 

--查所有父子结点
if object_id('f_getAll') is not null drop function f_getAll
go
create 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 @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return
end 
go  
 

--删除演示

drop table tb_bookInfo

drop table tb_bookType

drop function f_getP

drop function f_getC
drop function f_getAll
GO

--sqlserver2005的新方法

-- 建立演示环境
IF OBJECT_ID('[Dept]') IS NOT NULL
    DROP TABLE [Dept]
GO
CREATE TABLE Dept(
 id int PRIMARY KEY,
 parent_id int,
 name nvarchar(20))
INSERT Dept
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
--1、父-〉子
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS 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 DEPTS
GO
--结果如下
/*
id          parent_id   name                
----------- ----------- --------------------
6           4           MIS
7           6           UI
8           6           软件开发
9           8           内部开发

（所影响的行数为 4 行）
*/

--2、子-〉父
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'内部开发'
;WITH
DEPTS 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 DEPTS
GO

--结果如下
/*
id          parent_id   name                
----------- ----------- --------------------
9           8           内部开发
8           6           软件开发
6           4           MIS
4           0           业务部

（所影响的行数为 4 行）
*/

-- 删除演示环境
DROP TABLE Dept

[jmx123456789]

[Quote=引用 1 楼 acherat 的回复:]

[/Quote]
详细 mark

[a291410855]

用函数的话，我应该知道怎么写。有sql语句直接搞定的吗？或者效率更好的方法。我2000的数据库。

[黄_瓜]

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[name] varchar(5),[pid] int)
insert [tb]
select 1,'test1',0 union all
select 2,'test2',1 union all
select 3,'test3',1 union all
select 4,'test4',2 union all
select 5,'test5',3 union all
select 6,'test6',5
--------------开始查询--------------------------
;with t as 
(
select *,level=1 from [tb] where [pid]=0
union all
select tb.*, level=level+1 from [tb],t where tb.[pid]=t.[id]
)
select * from t
----------------结果----------------------------
/* 
id          name  pid         level
----------- ----- ----------- -----------
1           test1 0           1
2           test2 1           2
3           test3 1           2
5           test5 3           3
6           test6 5           4
4           test4 2           3

(6 行受影响)

*/

[黄_瓜]

[Quote=引用 3 楼 a291410855 的回复:]

用函数的话，我应该知道怎么写。有sql语句直接搞定的吗？或者效率更好的方法。我2000的数据库。
[/Quote]

2000的用个临时表函数什么的

[q806294478]

if OBJECT_ID('tb') is not null
drop table tb
create table tb (id int, name varchar(10),pid int)
insert into tb select 1, 'test1', 0 
union all select 2, 'test2', 1
union all select 3, 'test3', 1
union all select 4, 'test4', 2
union all select 5, 'test5', 3
union all select 6, 'test6', 5
go
with cte as(
select id,name,pid,1 as level from tb where pid=0
union all 
select  a.id,a.name,a.pid,b.level+1 from tb a join cte b on a.pid=b.id 
)
select * from cte
结果为：
id	name	pid	level
1	test1	0	1
2	test2	1	2
3	test3	1	2
5	test5	3	3
6	test6	5	4
4	test4	2	3

(6 行受影响)