if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([parentId] int,[channelId] int,[siteId] int,[chnlName] varchar(6))
insert [tb]
select 0,3473,13,'广州市' union all
select 3473,3474,13,'白云区' union all
select 3473,3475,13,'天河区' union all
select 3475,3476,13,'棠下' union all
select 3475,3477,13,'棠东' union all
select 3473,3478,13,'海珠区' union all
select 0,3479,13,'东莞市' union all
select 3479,3450,13,'市区' union all
select 3479,3451,13,'市政府'
go

;with cte as
(
  select * from tb where channelId=3473
  union all
  select b.* from cte a join tb b on b.parentid=a.channelid
)
select * from cte order by channelid

/**
parentId    channelId   siteId      chnlName
----------- ----------- ----------- --------
0           3473        13          广州市
3473        3474        13          白云区
3473        3475        13          天河区
3475        3476        13          棠下
3475        3477        13          棠东
3473        3478        13          海珠区

(6 行受影响)
**/