联 系 我 们
售前咨询
售后咨询
微信关注:星环科技服务号
更多联系方式 >
6.4.9 WITH…AS
更新时间:4/14/2022, 8:56:41 AM

当子查询嵌套层数较多,语句会难以阅读和维护。我们可以通过用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语句,不允许在其中穿插其他关键字。