WITH AS 使用

WITH AS 含义：

　　WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

　　特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。(百度文库)

 

WITH AS 使用：

数据结构及页面显示格式：

 

简单的嵌套语句：

SELECT  CombRulesID ,
        CombCode ,
        CombName ,
        ParentRuleID ,
        Child ,
        Depth
FROM      dbo.AS_CombRules
WHERE     CombRulesID IN ( SELECT CombRulesID
                         FROM   dbo.AS_CombRules
                         WHERE  ParentRuleID = 121)

 这只是一个简单的子查询，若是嵌套过多会浪费性能，还会给阅读性带来很大的不便，更会给维护性带来麻烦。下面我们写一个表变量的方式实现这段 SQL 做一个比较。

 

DECLARE @T TABLE ( temp NVARCHAR(3) );
INSERT  INTO @T
        ( temp
        )
        ( SELECT    CombRulesID
          FROM      dbo.AS_CombRules
          WHERE     ParentRuleID = 121
        );
SELECT  CombRulesID ,
        CombCode ,
        CombName ,
        ParentRuleID ,
        Child ,
        Depth
FROM    dbo.AS_CombRules
WHERE   CombRulesID IN ( SELECT *
                         FROM   @T )

 这个SQL看起来比上面的还要复杂，更麻烦。但是却能让SQL更容易维护。唯一遗憾的是，表变量引用了临时表，从而增加了额外的I/O开销，因此，表变量的方式不适合数据量大且频繁操作数据的情况。

那么问题来了，我们用什么方式实现能有好的阅读性、维护性及性能呢？答案是：CET(公用表表达式)。

 

用CET实现上面的SQL：

WITH    org
          AS ( SELECT   CombRulesID
               FROM     dbo.AS_CombRules
               WHERE    ParentRuleID = 121
             )
    SELECT  CombRulesID ,
            CombCode ,
            CombName ,
            ParentRuleID ,
            Child ,
            Depth
    FROM    dbo.AS_CombRules
    WHERE   CombRulesID IN ( SELECT *
                             FROM   org )

 看是不是又有可读性，又兼备维护性呢，有没有额外的I/O开销，一举多得啊。

 

注意：

在使用CTE时要注意如下几个问题：

1. CET后面必须直接使用CET的SQL语句(CRUD)，否则CET将会失效。

WITH    org
          AS ( SELECT   CombRulesID
               FROM     dbo.AS_CombRules
               WHERE    ParentRuleID = 121
             )
    SELECT  CombRulesID ,
            CombCode ,
            CombName ,
            ParentRuleID ,
            Child ,
            Depth
    FROM    dbo.AS_CombRules
    WHERE   ParentRuleID = 0;   -- 应将这条SQL直接去掉, 直接跟着后面的那条使用CTE的SQL      
SELECT  CombRulesID ,
        CombCode ,
        CombName ,
        ParentRuleID ,
        Child ,
        Depth
FROM    dbo.AS_CombRules
WHERE   CombRulesID IN ( SELECT *
                         FROM   org );

 异常信息：

 

 

WITH AS 递归：

WITH    ORG ( CombRulesID )
          AS ( SELECT   CombRulesID
               FROM     dbo.AS_CombRules
               WHERE    CombRulesID IN ( SELECT col
                                         FROM   Split('115', '_') ) --查询出当前附加分项的CombRulesID
               UNION ALL --必须包含 UNION ALL 关键字, 显示当前级别以下的所有有关的数据
               SELECT   CR.CombRulesID
               FROM     ORG --查询出附加分项的所有节点CombRulesID
                        INNER JOIN dbo.AS_CombRules CR ON CR.ParentRuleID = ORG.CombRulesID 
                        --递归,查询这个表中CR表中ParentRuleID等于ORG中的所有节点的CombRulesID
               WHERE    ExamType = 2
             )
    DELETE  dbo.AS_CombRules
    WHERE   CombRulesID IN ( SELECT CombRulesID
                             FROM   ORG )