[MSSQL2005]简单树型表结构

use master;
create database test;
go

use test;
create table Tree
(NodeID int primary key
,NodeName nvarchar(50)
,ParentID int
,Depth int
,IsLeaf bit
);
go

insert into Tree values(1,'a',0,0,0);
insert into Tree values(2,'b',1,1,0);
insert into Tree values(3,'c',2,2,1);
insert into Tree values(4,'d',2,3,1);
insert into Tree values(5,'e',1,4,0);
go

with v as
(
select * from Tree where NodeID in(select NodeID from Tree where ParentID=0)
union all
select Tree.* from Tree,v where v.NodeID=Tree.ParentID
)
select * from v
go

use master;
drop database test;
go