WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 

;WITH T1 AS 
(
    SELECT [UserID],[UserName],[Status],[Age] FROM Student WHERE [Status]=‘毕业‘
)
SELECT COUNT([UserID]) FROM T1 --正常运行
SELECT [UserID],[UserName],[Status],[Age] FROM T1 WHERE [Age]>18 --出错， CTE仅在其定义后紧接的下一条SQL语句中当中有效

;WITH T1 AS 
(
    SELECT [UserID],[UserName],[Gender] FROM Student
)
, T2 AS 
(
    SELECT [UserID],[UserName] FROM Teacher 
)
, T3 AS 
(
    SELECT [UserID],[UserName] FROM SchoolLeader 
)

WITH CTE1 AS 
(
    SELECT
        DepartmentID,
        DepartmentName,
        ParentID,
        CAST(DepartmentID AS varchar(MAX)) AS FullPathID,
        CAST(DepartmentName AS nvarchar(MAX)) AS FullPathName
    FROM Department AS D1
    WHERE (ISNULL(ParentID, ‘‘) = ‘‘) 
    UNION ALL 
    SELECT
        D2.DepartmentID,
        D2.DepartmentName,
        D2.ParentID,
        C.FullPathID + ‘>‘ + D2.DepartmentID AS FullPathID,
        C.FullPathName + ‘>‘ + D2.DepartmentName AS FullPathName
    FROM Department AS D2 
    INNER JOIN CTE1 AS C ON C.DepartmentID = D2.ParentID
)

SELECT
    CTE1.DepartmentID,
    CTE1.DepartmentName,
    CTE1.ParentID,
    CTE1.FullPathID,
    CTE1.FullPathName
FROM CTE1