日期:2014-05-17 浏览次数:20446 次
create table A(aID int identity(1,1),baseid int,[name] varchar(20))
create table B(bID int identity(1,1),parentId int,childId int)
insert A
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'a1' union all
select 5,'a2' union all
select 6,'b1' union all
select 7,'aa1' union all
select 8,'dsdssd'
insert B
select 1,4 union all
select 1,5 union all
select 4,7 union all
select 7,8
--create table A(aID int identity(1,1),baseid int,[name] varchar(20))
--create table B(bID int identity(1,1),parentId int,childId int)
--insert A
--select 1,'aaa' union all
--select 2,'bbb' union all
--select 3,'ccc' union all
--select 4,'a1' union all
--select 5,'a2' union all
--select 6,'b1' union all
--select 7,'aa1' union all
--select 8,'dsdssd'
--insert B
--select 1,4 union all
--select 1,5 union all
--select 4,7 union all
--select 7,8
WITH cte
AS ( SELECT parentId ,
childId ,
1 [LEVEL] --定义级别为顶层
FROM b
WHERE parentid = 1
UNION ALL
SELECT b.parentid ,
b.childId ,
a.[level] + 1 --计算B中的个个级别
FROM cte a
INNER JOIN B ON B.parentid = a.childId