日期:2014-05-18 浏览次数:20609 次
if OBJECT_ID('tb') is not null Drop table tb; go create table tb(nodeid int, parentid int, nodename varchar(32)) go insert into tb(nodeid, parentid, nodename) select 1, -1, '根' union all select 2, 1, 'A' union all select 3, 1, 'B' union all select 4, 1, 'C' union all select 5, 2, 'A-1' union all select 6, 2, 'A-2' union all select 7, 2, 'A-3' union all select 8, 3, 'B-1' union all select 9, 3, 'B-2' union all select 10, 4, 'C-1' union all select 11, 5, 'A-1-1' union all select 12, 5, 'A-1-2' union all select 13, 8, 'B-1-1' union all select 14, 8, 'B-1-2'; go declare @i int; set @i = 1; --传入的节点号 with T1 as ( select * from tb where tb.parentid = @i ), T2(rn, nodeid, parentid, nodename) as ( select rn = row_number() over(partition by tb.parentid order by tb.nodeid) ,tb.* from tb join T1 on tb.parentid = T1.nodeid ) select * from tb where nodeid = @i union all select * from T1 union all select nodeid, parentid, nodename from T2 where rn = 1 /* (14 行受影响) nodeid parentid nodename ----------- ----------- -------------------------------- 1 -1 根 2 1 A 3 1 B 4 1 C 5 2 A-1 8 3 B-1 10 4 C-1 (7 行受影响) */