当子查询嵌套层数较多，语句会难以阅读和维护。我们可以通过用WITH…AS定义公共表表达式（CTE）来简化查询，提高可阅读性和易维护性。

语法

WITH cte_name AS (select_statement) sql_containing_cte_name

说明

cte_name是公共表表达式的名字
select_statement是一个完整的SELECT语句
sql_containing_cte_name是包含了刚刚定义的公共表表达式的SQL语句，注意，定义了一个CTE以后必须马上使用，否则这个CTE定义将失效。

举例

WITH nv AS (
    SELECT name FROM user_info 
    JOIN transactions ON user_info.acc_num = transactions.acc_num
)
SELECT DISTINCT name FROM nv;

在WITH…AS连续定义多个CTE

用户可以通过一次WITH定义多个CTE，中间用逗号连接：

WITH cte_1 AS (select_statement_1),
     cte_2 AS (select_statement_2),
     cte_3 AS (select_statement_3),
     ...
     sql_containing_all_defined_ctes

说明

所有定义的cte必须都马上使用。
后定义的cte可以引用已经定义的cte。

举例

下例查询所有个人平均交易额大于所有平均交易额的用户名字

SELECT name FROM user_info 
JOIN (SELECT transactions.acc_num, AVG(price*amount) avg_trans 
      FROM transactions 
      GROUP BY transactions.acc_num) personal_avg 
      ON user_info.acc_num = personal_avg.acc_num 
      WHERE avg_trans > (SELECT AVG(price*amount) FROM transactions);

name

华*

李**

管**

祝**

用WITH…AS改写：

WITH 
    personal_avg AS (
    SELECT transactions.acc_num, AVG(price*amount)avg_trans 
    FROM transactions 
    GROUP BY transactions.acc_num),
     namelist AS (
         SELECT name FROM user_info JOIN personal_avg 
         ON user_info.acc_num = personal_avg.acc_num 
         WHERE avg_trans > (SELECT AVG(price*amount)FROM transactions))
SELECT * FROM namelist;

name

华*

李**

管**

祝**

注意，根据公共表达式的使用语法，WITH-AS短语必须紧接SELECT语句，不允许在其中穿插其他关键字。