SQL---公共表表达式（CTEs）

公共表表达式（CTEs）是一个命名的临时结果集。CTE不作为对象存储，仅在查询执行期间持续。

 

有时我们在处理一些复杂查询的时候，需要把查询内容一步步分解，最后计算出想要的结果。因此我们需要保存这些中间数据，但是计算出最后结果之后这些数据就没用了，因此我们需要把这些中间计算过程保存在一个临时的结果集里。

 

语法：

WITH cte_name AS (
    query
);

注：CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔。

 

例子：

在合并两张表之前先进行筛选，尽可能删减掉不需要的行，只保留需要的行，这样编写出的SQL更高效。

 

以下的SQL是非常低效的，因为它先合并两个表，之后才对“2017年1月9日”之后的时间进行筛选：

SELECT *
FROM table_a a
JOIN table_b b
ON a.username = b.username
WHERE a.day >= '2017-09-01'

 

正确的表达方式是在合并表之前使用CTEs进行筛选，如下：

WITH a AS (
     SELECT *
     FROM table_a
     WHERE day >= '2017-09-01'),

b AS (
     SELECT *
     FROM table_b
     WHERE day >= '2017-09-01');

SELECT *
FROM a
JOIN b
ON a.username=b.username;

 

CTEs会预存子查询的结果，所以运行速度通常比子查询要快。尤其是当某个子查询的表被重复使用的时候，效率会显著提升。