Non-Recursive Common Table Expressions Overview

公用表表达式(Common Table Expressions,CTEs)是标准SQL的一种特性，本质上是对临时结果集的命名。有两种类型的CTEs：非递归CTE，即本文内容；递归CTE。

非递归CTEs

使用WITH关键字来标识一个CTE。CTE需要给定一个名称，然后按下图格式给定查询表达式：

CTEs类似于派生表，例如：

WITH engineers AS 
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' )

SELECT * FROM engineers
WHERE ...

SELECT * FROM
   ( SELECT * FROM employees
     WHERE dept = 'Engineering' ) AS engineers
WHERE
...

非递归CTE基本上等价于一个视图VIEW。作为表表达式(也是一种表)它需要满足一些集合、关系的特性，因此它有一些有点，也有一些限制。例如，使用CTE比在FROM (SELECT ...)中嵌套派生表的可读性要强得多。 CTE可以在其他CTE中引用，也可以在其他很多地方引用。

CTE中引用其他CTE

使用此格式可以避免使用嵌套的派生表子句FROM(SELECT ...)，使其可读性更强。如下示例：

WITH engineers AS (      /* the first cte */
SELECT * FROM employees
WHERE dept IN('Development','Support') ),
eu_engineers AS (         /* the second cte */
SELECT * FROM engineers 
WHERE country IN('NL',...) )

SELECT             /* reference the second */
...
FROM eu_engineers;

CTE更多用途

可用于'anti-self join'，例如：

WITH engineers AS (
SELECT * FROM employees
WHERE dept IN('Development','Support') )

SELECT * FROM engineers E1
WHERE NOT EXISTS
   (SELECT 1 FROM engineers E2
    WHERE E2.country=E1.country
    AND E2.name <> E1.name );

或者，按年份进行比较，例如：

WITH sales_product_year AS (
SELECT product, YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year )

SELECT *
FROM sales_product_year CUR,
sales_product_year PREV,
WHERE CUR.product=PREV.product 
AND  CUR.year=PREV.year + 1 
AND CUR.total_amt > PREV.total_amt

另一用途是按组单独比较，如下：

WITH sales_product_year AS (
SELECT product,
YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year
)

SELECT * 
FROM sales_product_year S1
WHERE
total_amt > 
    (SELECT 0.1 * SUM(total_amt)
     FROM sales_product_year S2
     WHERE S2.year = S1.year)