说明
查询中可以使用 set 子句 或 WITH 子句 来创建通用表达式（Common Table Expression，简称CTE）。
CTE是一个临时结果集，在单个SQL语句执行期间存在于内存中，供SELECT查询重复引用。
CTE可以扁平化嵌套查询或者简化子查询，SELECT只需执行一遍子查询，提高查询性能。本文介绍如何在SELECT查询中使用WITH子句。
举例
- 以下几个查询等价:
// 如果有完全相同的子查询, 自动启用 cte 实际上只查询了一次t表
SELECT a, b FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x where a = 1
union all
SELECT a, b FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x where a = 2;
// 显示的指定启用 cte 写法一
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x where a = 1
union all
SELECT a, b FROM x where a = 2;
// 显示的指定启用 cte 写法二
WITH (SELECT a, MAX(b) AS b FROM t GROUP BY a) as x
SELECT a, b FROM x where a = 1
union all
SELECT a, b FROM x where a = 2;
// 使用 set 启动 cte
set @x@table = (SELECT a, MAX(b) AS b FROM t GROUP BY a);
SELECT a, b FROM @x@table where a = 1
union all
SELECT a, b FROM @x@table where a = 2;
- 注意: 如果查询中有 now() 等函数, 则不建议使用 cte。
