CTE-公用表表达式

###CTE(Common Table Expression)#### 即公用表表达式，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE View语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

####CTE可用于： ⒈创建递归查询

⒉在同一语句中多次引用生成的表 ####CTE优点： 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。

查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

####CTE可使用的范围： 可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

###简单公用表表达式 简单公用表表达式是在Select语句之前出现的包含了一张或多张派生表的列表。这些派生表将按照声明顺序进行创建。这意味着CTE元素只能引用在该元素之前声明的CTE元素。

With ItemSummary (upc, item_price_tot) AS
( Select upc, sum(item_price)
  from orderdetails
  group by upc)
--main query
select p1.upc, p1.item_price_tot
from itemsummary AS P1
Where p1.item_price_tot
= (select max(p1.item_price_tot)
   from itemsummary as p2);

###递归公用表表达式 公用表表达式还提供了一个递归选项。针对集合的递归定义包括两部分。固定的部分是初始元素，之后将通过对前一步的结果应用规则，一步步把更多的元素添加入集合中。这类结构的语法部分包含了Union/Union All子句。

####简单增量 递归CTE最常用于替代循环。例如，为了创建函数查找表（如函数f(i)),可编写如下代码：

with recursive increment (i, function_value)
as
(select i,j
   from (values (1, f(1))))
   union 
   select (i+1), f(i+1)
   from increment
   where (i+1)<=1000)
select i, function_value from increment;

####简单树遍历 The below contents are quoted from Teradata Document: #####SQL FUndamentals > SQL Data Defintion, Control, and Manipulation > Using the With Recursive Clause Consider the following employee table:

   CREATE TABLE employee
      (employee_number INTEGER
      ,manager_employee_number INTEGER
      ,last_name CHAR(20)
      ,first_name VARCHAR(30));

The table represents an organizational structure containing a hierarchy of employee-manager data. The following figure depicts what the employee table looks like hierarchically.

The following recursive query retrieves the employee numbers of all employees who directly or indirectly report to the manager with employee_number 801:

   WITH RECURSIVE temp_table (employee_number) AS
   ( SELECT root.employee_number
     FROM employee root
     WHERE root.manager_employee_number = 801
   UNION ALL
     SELECT indirect.employee_number
     FROM temp_table direct, employee indirect
     WHERE direct.employee_number = indirect.manager_employee_number
   )
   SELECT * FROM temp_table ORDER BY employee_number;

In the example, temp_table is a temporary named result set that can be referred to in the FROM clause of the recursive statement. The initial result set is established in temp_table by the nonrecursive, or seed, statement and contains the employees that report directly to the manager with an employee_number of 801:

   SELECT root.employee_number
   FROM employee root
   WHERE root.manager_employee_number = 801

The recursion takes place by joining each employee in temp_table with employees who report to the employees in temp_table. The UNION ALL adds the results to temp_table.

   SELECT indirect.employee_number
   FROM temp_table direct, employee indirect
   WHERE direct.employee_number = indirect.manager_employee_number

Recursion stops when no new rows are added to temp_table. The final query is not part of the recursive WITH clause and extracts the employee information out of temp_table:

   SELECT * FROM temp_table ORDER BY employee_number;

Here are the results of the recursive query: