sql server中怎么实现像oracle中的这种父子关系的树形结构？

[hdkkk]

----oracle中的表结构
SQL> desc dirs
Name Null? Type
----------------------------------------- -------- ----------------------------
DIR_ID NOT NULL NUMBER(6)
DIR_NAME NOT NULL VARCHAR2(30)
DIR_EXPLAIN VARCHAR2(400)
PARENT_ID NUMBER(4)
ACCESS_PRIVILEGE NUMBER
DIR_RESERVE1 NUMBER
DIR_RESERVE2 NUMBER
CREATED_DATE DATE
LAST_UPDATE DATE
-----表里的数据
SQL> select parent_id,dir_id,dir_name from dirs
2 ;

PARENT_ID DIR_ID DIR_NAME
---------- ---------- ------------------------------
0 27 test
0 21 fdg
0 43 fafd
0 45 th
43 47 你好_1
47 49 你好_2
57 59 发现
0 55 th
0 57 看看_xiugaai
0 71 目录说明_1
71 73 目录说明_2rename

PARENT_ID DIR_ID DIR_NAME
---------- ---------- ------------------------------
0 61 e
61 63 dd
63 65 ddd
65 67 dddd
67 69 ddddd
73 75 目录说明_3
0 81 first
81 85 second
85 87 third

20 rows selected.
-----树形结构如下：
SQL> select parent_id,dir_id,dir_name,LEVEL from dirs
2 START WITH PARENT_id=0
3 CONNECT BY PRIOR DIR_ID = PARENT_id
4 ORDER SIBLINGS BY DIR_NAME;

PARENT_ID DIR_ID DIR_NAME LEVEL
---------- ---------- ------------------------------ ----------
0 55 th 1
0 61 e 1
61 63 dd 2
63 65 ddd 3
65 67 dddd 4
67 69 ddddd 5
0 43 fafd 1
43 47 你好_1 2
47 49 你好_2 3
0 21 fdg 1
0 81 first 1

PARENT_ID DIR_ID DIR_NAME LEVEL
---------- ---------- ------------------------------ ----------
81 85 second 2
85 87 third 3
0 27 test 1
0 45 th 1
0 57 看看_xiugaai 1
57 59 发现 2
0 71 目录说明_1 1
71 73 目录说明_2rename 2
73 75 目录说明_3 3

20 rows selected.

SQL>

[CrazyFor]

http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.8570978

[pengdali]

/*--按父找子--*/
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')

declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1

[hdkkk]

大力，只有自己写程序吗？