SELECT 
        a.*,有无子部门=CASE WHEN b.部门代号 IS NULL THEN 1 ELSE 0 end
    FROM Tree AS a 
        OUTER APPLY(SELECT TOP 1 部门代号 FROM Tree WHERE a.部门代号=上级部门) AS b

------解决方案--------------------
SQL code

if(object_id('a')is not null) drop table a
CREATE TABLE A 
(
branchcode varchar(20),
branchname varchar(50),
managebranch varchar(50)
)
go
insert into a
select '03','采购部','' union all
select '0301','采购部1','03' union all
select '0302','采购部2','03' union all
select '030101','采购部1-2','0301' union all
select '030201','采购部2-2','0302'

select branchcode,branchname,case when branchcode in(select managebranch from a where managebranch is not null and len(managebranch)<>0) then 1 else 0 end as ifsonbranch from a

/*
03    采购部     1
0301    采购部1     1
0302    采购部2     1
030101    采购部1-2 0
030201    采购部2-2     0

*/

------解决方案--------------------
SQL code
if(object_id('a')is not null) drop table a
CREATE TABLE A 
(
branchcode varchar(20),
branchname varchar(50),
managebranch varchar(50)
)
go
insert into a
select '03','采购部','' union all
select '0301','采购部1','03' union all
select '0302','采购部2','03' union all
select '030101','采购部1-2','0301' union all
select '030201','采购部2-2','0302'


SELECT *,CASE WHEN EXISTS(SELECT 1 FROM A WHERE managebranch=T.branchcode) THEN 0 ELSE 1 END FROM A T
/*
（所影响的行数为 5 行）

branchcode           branchname                                         managebranch                                                   
-------------------- -------------------------------------------------- -------------------------------------------------- ----------- 
03                   采购部                                                                                                   0
0301                 采购部1                                               03                                                 0
0302                 采购部2                                               03                                                 0
030101               采购部1-2                                             0301                                               1
030201               采购部2-2                                             0302                                               1

（所影响的行数为 5 行）

------解决方案--------------------
SQL code


if(object_id('a')is not null) drop table a
CREATE TABLE A 
(
branchcode varchar(20),
branchname varchar(50),
managebranch varchar(50)
)
go
insert into a
select '03','采购部','' union all
select '0301','采购部1','03' union all
select '0302','采购部2','03' union all
select '030101','采购部1-2','0301' union all
select '030201','采购部2-2','0302'

select branchcode,branchname,case when branchcode in(select managebranch from a where managebranch is not null and len(managebranch)<>0) then 0 else 1 end as ifsonbranch from a

/*
03    采购部    0
0301    采购部1    0
0302    采购部2    0
030101    采购部1-2    1
030201    采购部2-2    1

*/