复用子查询

功能描述

复用子查询（Common Table Expression，CTE）通过 WITH ... AS 语句定义命名的临时结果集，在查询的不同部分重复使用，避免重复计算相同的子查询，从而提高查询性能和可读性。

语法格式

单个子查询：

WITH cte_name AS (
     SELECT ...
     FROM table_name
     WHERE ...
 )
 SELECT ...
 FROM cte_name
 WHERE ...; 

多个子查询：

WITH cte_name1 AS (
     SELECT ...
     FROM table_name
     WHERE ...
 ), 
cte_name2 AS (
     SELECT ...
     FROM table_name
     WHERE ...
 )
 SELECT ...
 FROM cte_name1, cte_name2
 WHERE ...; 

递归CTE：

WITH RECURSIVE cte_name AS (
     SELECT ... FROM ...          -- 基础查询
     UNION ALL
     SELECT ... FROM cte_name ... -- 递归查询
) 
SELECT ... FROM cte_name;

参数说明

注意事项

• 所要查询的表必须是已经存在的表，否则会出错。
• 多个 CTE 之间使用逗号分隔。
• CTE 定义必须在主查询之前。
• CTE 只在其所在的 SELECT 语句中有效，不能跨查询使用。
• 递归 CTE 必须包含锚点查询和递归查询两部分，用 UNION ALL 连接。

示例

• 示例 1：基于表 sales 定义子查询 sales_data，查询销售金额大于 100 的商品，最后查询该子查询的所有数据。

  WITH sales_data AS ( 
    SELECT product_name, sales_amount
    FROM sales
    WHERE sales_amount > 100
  )
  SELECT * FROM sales_data; 

• 示例 2：基于表 sales 定义子查询 sales_data1 和 sales_data2，其中 sales_data1 查询销售金额大于 100 的商品，sales_data2 查询销售金额小于 20 的商品，最后合并两个子查询的数据。

  WITH sales_data1 AS (
     SELECT product_name, sales_amount
     FROM sales
     WHERE sales_amount > 100
  ),
  sales_data2 AS (
     SELECT product_name, sales_amount
     FROM sales
     WHERE sales_amount < 20
  ) 
  SELECT * FROM sales_data1 
  UNION ALL
  SELECT * FROM sales_data2; 

• 示例 3：递归CTE生成1到10的序列。

  WITH RECURSIVE nums AS (
     SELECT 1 AS n
     UNION ALL
     SELECT n + 1 FROM nums WHERE n < 10
  ) 
  SELECT * FROM nums;