testbook

WITH...AS

当子查询嵌套层数较多，语句会难以阅读和维护。我们可以通过用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定义将失效。

举例

[$host] transwarp> WITH nv AS (SELECT name FROM user_info JOIN transactions ON acc_num = 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。

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

[$host] transwarp> 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);
祝韩恒
华微
李韩瑶
管淑艳

用WITH...AS改写：

[$host] transwarp> 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;
祝韩恒
华微
李韩瑶
管淑艳