SQL with as语法（CTE公用表表达式）

介绍

WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式（CTE）。
公用表表达式是一个命名的临时结果集，仅在单个SQL语句 (例如 SELECT ， INSERT ， UPDATE 或 DELETE)的执行范围内存在。 与 派生表 类似， CTE 不作为对象存储，仅在查询执行期间持续。 与派生表不同， CTE 可以是自引用 (递归CTE)，也可以在同一查询中多次引用。

语法

第一，SELECT，UPDATE和DELETE语句的开头可以使用WITH子句：

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

第二，可以在子查询或派生表子查询的开头使用WITH子句：

SELECT ... WHERE id IN (WITH ... SELECT ...);

SELECT * FROM (WITH ... SELECT ...) AS derived_table;

第三，可以在SELECT语句之前立即使用WITH子句，包括SELECT子句：

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

其他注意要点：

1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：
2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的例子2）所示。
3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句：

--  table1是一个实际存在的表
with table1 as
(
    select * from persons where age < 30
)
select * from table1  --  使用了名为table1的公共表表达式
select * from table1  --  使用了名为table1的数据表

例子

1）简单例子

下面例子中，CTE的名称为customers_in_usa，定义CTE的查询返回两列：customerName和state。因此，customers_in_usa CTE返回位于美国的所有客户。
在定义美国CTE的客户之后，我们可在SELECT语句中引用它，例如，仅查询选择位于CA 的客户

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;

2）使用两个CTE的例子

WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;

参考资料：
https://www.cnblogs.com/Niko12230/p/5945133.html
https://www.yiibai.com/mysql/cte.html#:~:text=%E5%85%AC%E7%94%A8%E8%A1%A8%E8%A1%A8%E8%BE%BE%E5%BC%8F%E6%98%AF%E4%B8%80%E4%B8%AA%E5%91%BD%E5%90%8D%E7%9A%84%E4%B8%B4%E6%97%B6%E7%BB%93%E6%9E%9C%E9%9B%86%EF%BC%8C%E4%BB%85%E5%9C%A8%E5%8D%95%E4%B8%AASQL%E8%AF%AD%E5%8F%A5%20%28%E4%BE%8B%E5%A6%82%20SELECT%20%EF%BC%8C%20INSERT%20%EF%BC%8C%20UPDATE%20%E6%88%96,%E6%B4%BE%E7%94%9F%E8%A1%A8%20%E7%B1%BB%E4%BC%BC%EF%BC%8C%20CTE%20%E4%B8%8D%E4%BD%9C%E4%B8%BA%E5%AF%B9%E8%B1%A1%E5%AD%98%E5%82%A8%EF%BC%8C%E4%BB%85%E5%9C%A8%E6%9F%A5%E8%AF%A2%E6%89%A7%E8%A1%8C%E6%9C%9F%E9%97%B4%E6%8C%81%E7%BB%AD%E3%80%82%20%E4%B8%8E%E6%B4%BE%E7%94%9F%E8%A1%A8%E4%B8%8D%E5%90%8C%EF%BC%8C%20CTE%20%E5%8F%AF%E4%BB%A5%E6%98%AF%E8%87%AA%E5%BC%95%E7%94%A8%20%28%E9%80%92%E5%BD%92CTE%29%EF%BC%8C%E4%B9%9F%E5%8F%AF%E4%BB%A5%E5%9C%A8%E5%90%8C%E4%B8%80%E6%9F%A5%E8%AF%A2%E4%B8%AD%E5%A4%9A%E6%AC%A1%E5%BC%95%E7%94%A8%E3%80%82