用公共表表达式完成一些对特殊部门的操作

[sql] 
--题目：如下面, 求出所有顶级下的子级（仅子级，不含再下一级），要求是子级或者子级的下面不包含学校  
--顶级  
------处长室  
------------科长室0  
------------科长室1  
------宣传部  
------科苑小学  
------初中部  
------------兰州中学  
------高中部  
------------兰州高中  
--1. 创建部门表及测试数据  
IF OBJECT_ID('Dep') IS NOT NULL  
BEGIN  
    DROP TABLE Dep  
END  
GO  
CREATE TABLE Dep(  
    id INT,  
    pId INT,  
    [name] VARCHAR(200),      
    isSchool BIT    /*是否为学校*/  
)  
GO  
INSERT INTO Dep  
SELECT 1,0,'顶级',0 union  
SELECT 2,1,'处长室',0 union  
SELECT 3,2,'科长室0',0 union  
SELECT 4,2,'科长室1',0 union  
SELECT 5,1,'宣传部',0 union  
SELECT 6,1,'科苑小学',1 union  
SELECT 7,1,'初中部',0 union  
SELECT 8,7,'兰州中学',1 union  
SELECT 9,1,'高中部',1 union  
SELECT 10,9,'兰州高中',0  
  
--SELECT * FROM Dep d  
  
--2. 创建查询所有子级单位的函数  
IF OBJECT_ID('fn_getSubDep') IS NOT NULL  
BEGIN  
    DROP FUNCTION dbo.fn_getSubDep  
END  
GO  
  
CREATE FUNCTION fn_getSubDep( @depId INT )  
RETURNS TABLE  
AS  
RETURN(  
    WITH cte(id,pId,NAME,isSchool, level)  
    AS  
    (  
        SELECT id,pid,NAME,isSchool, 0  
        FROM Dep d WHERE id=@depId  
        UNION ALL  
        SELECT d2.id,d2.pid,d2.NAME,d2.isSchool, cte.[level]+1   
        FROM Dep d2 JOIN cte ON d2.pid = cte.id   
    )  
    SELECT * FROM cte  
)  
GO  
--SELECT * FROM dbo.fn_getSubDep(2)  
  
--3. 求出所有顶级下的子级（仅子级，不含再下一级），要求是子级或者子级的下面不包含学校  
WITH cte(id,pId,NAME,isSchool)  
AS  
(     
    SELECT id,pId,NAME,isSchool FROM dbo.fn_getSubDep(1) t1  
    WHERE LEVEL=1 AND isSchool=0  
    AND NOT EXISTS(  
        SELECT 1 FROM dbo.fn_getSubDep(t1.id) WHERE isSchool=1    
    )  
)  
SELECT * FROM cte