SQL SERVER判断部门有无子部门如何写?
部门代号 部门名称 上级部门
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


[解决办法]

SQL code

    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 aCREATE TABLE A (branchcode varchar(20),branchname varchar(50),managebranch varchar(50))goinsert into aselect '03','采购部','' union allselect '0301','采购部1','03' union allselect '0302','采购部2','03' union allselect '030101','采购部1-2','0301' union allselect '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    采购部     10301    采购部1     10302    采购部2     1030101    采购部1-2 0030201    采购部2-2     0*/
[解决办法]
SQL code
if(object_id('a')is not null) drop table aCREATE TABLE A (branchcode varchar(20),branchname varchar(50),managebranch varchar(50))goinsert into aselect '03','采购部','' union allselect '0301','采购部1','03' union allselect '0302','采购部2','03' union allselect '030101','采购部1-2','0301' union allselect '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                   采购部                                                                                                   00301                 采购部1                                               03                                                 00302                 采购部2                                               03                                                 0030101               采购部1-2                                             0301                                               1030201               采购部2-2                                             0302                                               1（所影响的行数为 5 行）
[解决办法]
SQL code
if(object_id('a')is not null) drop table aCREATE TABLE A (branchcode varchar(20),branchname varchar(50),managebranch varchar(50))goinsert into aselect '03','采购部','' union allselect '0301','采购部1','03' union allselect '0302','采购部2','03' union allselect '030101','采购部1-2','0301' union allselect '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    采购部    00301    采购部1    00302    采购部2    0030101    采购部1-2    1030201    采购部2-2    1*/