SQL SERVER判断部门有无子部门如何写?

[season8862008]

部门代号 部门名称 上级部门
03 采购部
0301 采购部1 03
0302 采购部2 03
030101 采购部1-2 0301
030201 采购部2-2 0302


我想用一句SQL 语句得到 有子部门为0,没有子部门的为1

部门代号 部门名称 有无子部门
03 采购部 0
0301 采购部1 0
0302 采购部2 0
030101 采购部1-2 1
030201 采购部2-2 1

[csdnTimePeriod]

select 部门代号,部门名称,case when exists(select 1 from 部门及子部门 where 上级部门代号=a.部门代号) then 0 else 1 end as 是否有子部门
from 部门及子部门 a

另外，字段“有无子部门”如果有子部门则为1，没有子部门则为0，更好理解。

[中国风]

    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

[极品老土豆]

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

*/

[SQL77]

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 行）

[极品老土豆]

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

*/

[Barton]

--o(︶︿︶)o 我来写写这样的吧
;with cte
as
(
	select '03' as [部门代号],'采购部' as [部门名称],'0' as [上级部门代号] 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 distinct a.[部门代号],a.[部门名称],case when b.[上级部门代号] is null then 1 else 0 end as [有无子部门]
from cte a left join cte b on a.[部门代号]=b.[上级部门代号]

[ChangeMyself2012]

[Quote=引用 3 楼 的回复:]

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
selec……
[/Quote]

3楼给力。