WITH cte_name AS (select_statement) sql_containing_cte_name
复制
当子查询嵌套层数较多,语句会难以阅读和维护。我们可以通过用WITH…AS定义公共表表达式(CTE)来简化查询,提高可阅读性和易维护性。
语法
说明
-
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语句,不允许在其中穿插其他关键字。 |
文档反馈