Non-Recursive Common Table Expressions Overview

You are viewing an old version of this article. View the current version here.

1. 非递归CTEs

非递归CTEs

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中引用其他CTE

```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更多用途

```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)
```