WITH (Common Table Expressions)

一、普通CTE

WITH可以为临时查询结果命名，命名后可以在后续的查询语句中反复引用。相当于一个临时的结果集

语法：

WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
SELECT ... FROM cte_name ...;

例子：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

继续阅读MySQL公共表表达式 →