WITH关键字之公用表表达式（CTE, Common Table Expression)

申明：本文知识主要要来源于英文版MSDN，是笔者自己对该知识点的理解，难免有错误的地方。如果您有不同的看法和意见，请跟帖提出。请友善交流，谢绝谩骂。

以下所有例句的执行环境为SQLEXPRESS2008。

一、CTE（公共表表达式）的定义
　　1.指定了临时名称的结果集称为公共表表达式，也简称为CTE（Common Table Expression）。
　　   我们来看一个简单的CTE例子，如例句1：

        WITH MyCTE (Id) AS
        (
            SELECT 123 AS Id
        )
        SELECT Id FROM MyCTE

　　　　　　例句1

 

　　2.CTE（公共表表达式）源自于简单查询，并且只能在单个SELECT,UPDATE或DELETE语句的执行范围内定义CTE(公共表表达式)。
　　   错误的CTE定义&用法，如例句2：

        WITH MyCTE (Id) AS
        (
            SELECT 123 AS Id
        )
        SELECT Id FROM MyCTE--之后的语句再使用到MyCTE，就会报错
        --注意：此句之后还可以定义其他语句，只是不能再使用MyCTE了，请正确理解。
        SELECT Id INTO #temp FROM MyCTE

　　　　　　例句2

　　　由于只能在单个SELECT,UPDATE或DELETE语句的执行范围内定义CTE(公共表表达式)，正确的SQL如例句1。

 

　　3.CTE(公共表表达式)也可以用在CREATE VIEW（视图定义）的SELECT语句部分。CTE(公共表表达式)可以自我嵌套，我们称之为递归的CTE(公共表表达式)。

　　   我们先看看CTE的语法结构：

    WITH expression_name (colum1,colum2,...,columN) AS
    (
        CTE_query_definition
    )

　　   参数：
　　　　expression_name
　　　　是用户给出的CTE(公共表表达式)的名称，必须是一个符合命名规范的标识符。expression_name不能与当前WITH子句中的其他CTE(公共表表达式)的名  称相同，但是expression_name可以与数据库中的基础表或视图的名称相同。查询中任何使用到expression_name标识符的地方，都是指CTE(公共表表达式)，而不是数据库中的基础表或视图。
　　　　columnN
　　　　是CTE（公共表表达式）中指定的列名。单个CTE（公共表表达式）中不允许出现相同的列。列的数量必须与CTE_query_definition的结果集的列数量相同。当CTE_query_definition结果集中所有的列不重复并且给出所有的列名，那么CTE（公共表表达式）中的列列表(colum1,colum2,...,columN)可以省略掉。

省略列列表的写法，如例句3：

        WITH MyCTE AS
        (
            SELECT 123 AS Id
        )
        SELECT Id FROM MyCTE

　　　　　　例句3
　　　　再看两种错误的写法，如例句4和例句5：

        WITH MyCTE AS
        (
            SELECT 123--结果集没有给出列名，省略掉(Id)会报错
        )
        SELECT Id FROM MyCTE

　　　　　　例句4

        WITH MyCTE (Id) AS
        (
            SELECT 123 AS Id ,'张三' AS Name--结果集给出的列数量比CTE指定的多
        )
        SELECT Id，Name FROM MyCTE

　　　　　　例句5

　　　　例句4的正确写法如例句1。例句5的正确写法如下例句6。

        WITH MyCTE (Id,Name) AS
        (
            SELECT 123 AS Id ,'张三' AS Name
        )
        SELECT Id,Name FROM MyCTE

　　　　　　例句6

　　　　CTE_query_definition
　　　　是一个SELECT语句，它产生的结果集用于填充CTE（公共表表达式）。这个SELECT语句与CREATE VIEW(定义视图)时的SELECT语句一样，必须满足那些必要的条件。不能使用一个CTE（公共表表达式）来定义另一个CTE（公共表表达式）。
　　　　如果CTE_query_definition有多个查询语句，必须使用UNION ALL,UNION,EXCEPT,或者INTERSECT其中的一个连接词来连接，请看例句7。

        WITH MyCTE (Id,Name) AS
        (
            SELECT 123 AS Id ,'张三' AS Name
            UNION ALL 
            SELECT 456 AS Id ,'李四' AS Name
        )
        SELECT Id,Name FROM MyCTE

　　　　　　例句7

 

二、定义和使用CTE（公共表表达式）的规则 & 定义和使用递归CTE（公共表表达式）的规则

　　1.定义和使用CTE（公共表表达式）的规则
　　　　(1).CTE后面衔接的必须是至少引用了CTE中一个列(column)的、单个的SELECT,INSERT,UPDATE,或DELETE语句。CTE也可以用在CREATE VIEW(视图定义)的SELECT语句中。
　　　　(2).CTE的中可以有多个CTE_query_definition，它们必须使用UNION ALL,UNION,INTERSECT,或EXCEPT中的一个来连接，如例句7。
　　　　(3).在同一个WITH子句中，CTE语句可以引用它本身以及引用在它之前被定义的CTE。提前引用后面即将被定义的CTE是不允许的。
　　　　(4).一个CTE中不能有多个WITH子句。例如，如果一个CTE_query_definition包含一个子查询，那么子查询中不能使用WITH子句定义一个嵌套的CTE。
　　　　(5).在CTE_query_definition中不允许出现下列项：
　　　　　　COMPUTE 或 COMPUTE BY
　　　　　　ORDER BY(除非指定了TOP子句)
　　　　　　INTO
　　　　　　有查询提示的OPTION子句
　　　　　　FOR XML
　　　　　　FOR BROWSE
　　　　(6).在批处理的语句中使用CTE（公共表表达式）时，CTE（公共表表达式）的前一个语句必须以分号";"结尾。请看下面例句8和例句9。

            SELECT 'Test' AS Word
            WITH MyCTE AS
            (
                SELECT 123 AS Id
            )
            SELECT Id FROM MyCTE

　　　　　　　　例句8

　　　　例句8的写法错误，执行时会直接报语法错误，正确的写法如例句9。

            SELECT 'Test' AS Word;
            WITH MyCTE AS
            (
                SELECT 123 AS Id
            )
            SELECT Id FROM MyCTE

　　　　　　　　例句9

　　　　(7).包含了CTE（公共表表达式）的查询语句可以用来定义游标。
　　　　(8).CTE（公共表表达式）可以引用远程服务器上的表。
　　　　(9).在执行CTE（公共表表达式）语句时，如果该CTE（公共表表达式）查询语句访问了基础表或视图，那么任意一个引用了该CTE（公共表表达式）的提示都可能与其他的提示发生冲突。如果发生了冲突，查询语句将会报错。

 

　　2.定义和使用递归CTE（公共表表达式）的规则
　　　　定义递归CTE（公共表表达式）的规则
　　　　(1).递归CTE的定义中必须至少有两个CTE查询语句，一个定点成员和一个递归成员。可以定义多个定点成员和多个递归成员，并且定点成员必须在递归成员之前定义。除非引用了CTE本身，否则所有的CTE_query_definition都是定点成员。
　　　　(2).定点成员之间必须使用UNION ALL,UNION,INTERSECT,或EXCEPT中的一个连接词来连接。需要连接多个递归成员时，仅仅只能使用UNION ALL这个集合运算符来连接末尾的定点成员和开头的递归成员。
　　　　(3).定点成员与递归成员中的列的数量必须一致。
　　　　(4).定点成员与递归成员中相对应的列的数据类型必须一致。
　　　　(5).递归成员的FROM子句只能引用expression_name一次（only one time）。
　　　　(6).在递归成员的CTE_query_definition中，不允许出现下列项：
　　　　　　SELECT DISTINCT
　　　　　　GROUP BY
　　　　　　HAVING
　　　　　　数量聚合
　　　　　　TOP
　　　　　　LEFT,RIGHT,OUTER JOIN(允许出现INNER JOIN)
　　　　　　子查询
　　　　　　CTE_query_definition中递归引用了CTE的提示

　　　　使用递归CTE（公共表表达式）的规则
　　　　(1).递归CTE返回的所有列都是可为null的，无论这些列本身是否可以为null。
　　　　(2).错误的CTE组合可能会导致死循环。例如，递归成员查询定义了对父查询的列和子查询的列返回相同值，那么就会产生一个死循环。为了防止产生死循环，可以在INSERT,UPDATE,DELETE或SELECT语句的OPTION子句中使用MAXRECURSION提示来限制递归的深度，MAXRECURSION值的范围是0至32,767。在你解决死循环的错误之前，这种方式可以让你控制语句的执行。MAXRECURSION在服务器中默认值是100。如果指定的是0，代表对递归深度不作限制。在每个语句中只能指定一次MAXRECURSION。
　　　　(3).包含递归CTE的视图不能用于更新数据。
　　　　(4).使用了CTE的查询可以用于定义游标。CTE是定义游标结果集的select_statement参数。递归CTE只允许使用快速只进游标和静态（快照）游标。如果在递归CTE中指定了其他游标类型，那么当前游标类型会被转化成静态游标类型。
　　　　(5).CTE中能够引用远程服务器中的表。如果CTE中的递归成员引用了远程服务器，将会为每个远程服务器上的表创建一个假脱机，这样就可以在本地重复使用这些表了。
　　　　(6).SQL Server2008中，CTE的递归部分不允许应用分析和集合函数。