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，短时间内很难搞清楚它真正的意图。
代码
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来代替子查询。
代码
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并不需要像表变量一样，在使用前需要定义好表结构，并且具有维护性高的优点。
