SQL CTE

由于程序员所在公司环境不太一样,所以造成程序员在IT技能方面有比较大的差别。例如有些公司，程序员大多只关心业务逻辑处理或者是页面UI，当然我指的是web应用程序相关的程序员。因为有可能所有的底层数据都由其它同事给你提供，这样就造成一旦需要自己写数据库脚本查询时，那些难以想像的,丑陋的SQL就出来了，不过如果我们稍加注意，就会知道SQL 2005的CTE在很大程序上能解决这种境况，这就看程序员的自身提高意识了。

      一提到SQL的CTE，大家很有可能第一反应就是它强大的递归查询，但我这里并不想讲这方面知识。

      先来看下它的定义：指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

      CTE分类：1：递归CTE；2：非递归CTE。         我今天总结的就是第二种，上面提到过CTE的合理利用可以避免出现丑陋的SQL，先贴一条嵌套查询的SQL：我想大家看到这样的SQL，短时间内很难搞清楚它真正的意图。

  

SELECT  r.room_type AS room_type,         a.* FROM    ( SELECT    f.id,                     end_date,start_date,                     ( f.room_num - ISNULL(( SELECT  SUM(a.free_room_used)                                             FROM    C a                                             WHERE   a.free_room_id = f.id                                             GROUP BY a.free_room_id                                           ), 0) ) AS free_num           FROM      A f           WHERE     ( f.deleted <>1                       OR f.deleted IS NULL                     )                     AND '2010-3-16' BETWEEN f.start_date AND f.end_date         ) a         INNER JOIN B r ON r.room_type_id = a.room_type_id                                       AND ( r.notes ='all'                                             OR r.notes ='40101035'                                           ) WHERE   a.free_num >0 ORDER BY a.end_date,         a.start_date

 

      我们再来看看CTE改善后的写法：可以看出没有了嵌套,分别用两个CTE来代替子查询。

 

BEGIN        WITH    freeroom               AS ( SELECT   SUM(a.free_room_used) AS used,                             free_room_id                    FROM     C a                             INNER JOIN B f ON a.free_room_id = f.id                    GROUP BY a.free_room_id                  ) ,             freehotel               AS ( SELECT   r.room_type AS room_type,                             f.id,                             start_date,end_date,                             ( f.room_num - ISNULL(fr.used, 0) ) AS free_num                    FROM     A f                             LEFT JOIN freeroom fr ON f.id = fr.free_room_id                             INNER JOIN B r ON r.room_type_id = f.room_type_id                                                           AND ( r.notes ='all'                                                                 OR r.notes ='40101035'                                                               )                    WHERE    ( f.deleted <>1                               OR f.deleted IS NULL                             )                             AND '2010-3-16' BETWEEN f.start_date AND f.end_date                  )         SELECT  *         FROM    freehotel         WHERE   free_num >0         ORDER BY end_date,         start_date END

 

       第一：尽量避免在查询字段，例如上例中取free_num,直接嵌套查询，这种写法看着就让人不舒服。                改进：把这个子查询用一个CET表达式表示出来，见上面的freeroom。

 

       第二：上述示例中，由于free_num这个字段并不是表中实际存在的字段，是通过计算等到的，所以要想以它做为where条件，不得不再嵌套一次。

               改进：我们把上面的子表也用CTE表示出来，见上面的freehotel。

 

       第三：直接查询freehotel,然后加上查询条件以及排序语句即可。

 

       小结：通过以上三步，就把上面嵌套了两层的查询给分清楚了，化整为零后再分析，思路就清晰多了。     

       注意事项:这是针对的都是非递归CTE

 

        1:CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分，否则CTE会失效。

        2：不允许在一个 CTE 中指定多个 WITH 子句。

 

        3：CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

            像上面例子中同一with下面，事先定义了freeroom,后面的freehotel使用了前面的freeroom。 

  

        4:不能在 CTE_query_definition 中使用以下子句：

         COMPUTE 或 COMPUTE BY

         ORDER BY（除非指定了 TOP 子句）          INTO

         带有查询提示的 OPTION 子句          FOR XML          FOR BROWSE

       5:如果将CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。

          这种说法的另外一种做法就是，让整个cte定义以及最后的操作，都用begin,end begin块包含起来，这样可以避免和其它语句形成一个批处理。

 

     CTE与表变量的关系：非递归的CTE在很大程序上能代替表变量，当然唯一的不同点在于，表变量能够在整个查询过程中有效，而CTE后面需要马上进行调用,但好处在于节约开销，因为表变量是会额外增加系统I/O开销。而且CTE并不需要像表变量一样，在使用前需要定义好表结构，并且具有维护性高的优点。