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 行受影响)
*/