Non-Recursive Common Table Expressions Overview

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

MariaDB starting with 10.2.1

CTE是MariaDB 10.2.1引入的特性。

非递归CTEs

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

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)

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.