哦，需要 利用一个化树为平板的子查询作为 条件
where deptid in (select parentid from (subq) where deptid='A1') or deptid='A1'

subq=
with t as (
select 
a.parentid,a.deptid
from tbA a

union all

select
b.parentid,a.deptid
from t a
inner join tbA b on a.parentid=b.deptid
)

------解决方案--------------------

SQL code

if object_id('tempdb..#T') is not null
    drop table #T
select * into #T from(
select deptid='A1',parentid=''  union 
select deptid='A2',parentid='A1' union  
select deptid='A3',parentid='A2' union
select deptid='A4',parentid='A3' 
)t

;with cte as(
    select deptid,parentid,cd=cast(right('0000'+deptid,4)as varchar(1000)) from #t where parentid='' 
    union all 
    select a.deptid,a.parentid,cd=cast(cte.cd+right('0000'+a.deptid,4) as varchar(1000))
    from #t a inner join cte on a.parentid=cte.deptid
)
select * from cte where cd like '00A1%'