sqlserver-用 WITH 和 UNION(或者UNION ALL)实现递归查询

WITH

WITH提供了一种撰写用于更复杂查询辅助语句的方法。这些通常被称为公用数据表表示式或CTE（Common Table Expressions）的宣告可以被想成是定义仅存在于一个查询中的临时数据表。WITH子句中的每个辅助语句都可以是SELECT、INSERT、UPDATE或DELETE；并且WITH子句本身附加到SELECT、INSERT、UPDATE或DELETE的主语句。

SELECT in WITH

SELECT中WITH的基本价值是将复杂的查询分解为较为简单的部分。例子如下：

WITH regional_sales AS（
SELECT region,SUM（amount）AS total_sales
FROM orders
GROUP BY region
）,top_regions AS（
SELECT region
FROM regional_sales
WHERE total_sales >（SELECT SUM（total_sales）/10 FROM regional_sales）
）
SELECT region,
product,
SUM（quantity）AS product_units,
SUM（amount）AS product_sales
FROM orders
WHERE region IN（SELECT region FROM top_regions）
GROUP BY region,product;

其中仅显示最上层销售区域中的每个产品的销售总计。WITH子句定义了两个名为regional_sales和top_regions的辅助语句，其中top_size使用region_sales的输出，top_regions的输出在主SELECT语句中使用。这个例子本来可以不用WITH编写，但是我们需要两层的SELECT子查询。按照这种方式更容易一些。

递归循序

一个非常简单的例子是这个查询来从1到100的整数求和：

WITH t3 AS 
(
SELECT 0 as val
UNION ALL
SELECT val+1 as val 
FROM t3 
WHERE  val <100 
)
select sum(val) as sum from t3

结果：

递归查询通常用于处理分层或树状结构的数据。
一个实用的例子是，一种多层级结构的部门结构，要查询某部门与该部门底下的所有子部门，和子部门下的子部门的部门ID和部门名称。
一张简单的部门表如下：

有两种情况：
1.有卡层次,例如卡最多三层，部门1->部门1.1->部门1.1.1
这种情形的可以选择left jion的方式:

2.没有卡层， 在创建部门的时候不进行层级的卡控，这个时候是没有办法选择用left join来实现，可以考虑用递归的方式，我们这时可使用WITH：

这样就优美的完成了递归循环！！
总结：前两周第一次看到这种写法的时候，确实被惊艳到了！觉得很酷！不过一直都没有在实际中用到，在前几天刚好碰到有个不卡层级的部门结构需要查询部门与所有子部门的一些资料，脑袋就突然想到这个写法！最后完美的实现功能，所以想开通第一篇博客来记录下来！

参考：
遞迴查詢（Common Table Expressions）