PostgreSQL WITH

定义

WITH 子句有助于将复杂的大型查询分解为更简单的表单，被 WITH 语句定义的语句成为【通用表表达式】（Common Table Express，CTR），也可以当做一个为了查询而存在的临时表。

示例

WITH w1 AS (
  SELECT id,name
  FROM t_table
	where id=1
), w2 AS (
  SELECT id,name
  FROM t_table2
  WHERE id=1
)
SELECT
  tl.name,
  w1.name as w1_name,
  w2.name as w2_name
FROM t_table3 as tl,w1, w2
WHERE tl.id=w1.id or tl.id2=w2.id

说明

WITH 关键字后面为CTR表达式名称，也可以成为临时表名称。多个临时表时，需用逗号分开。 

在 WITH 中使用数据修改语句 

 WITH moved_rows AS (
  DELETE FROM products
  WHERE
    "date" >= '2010-10-01'
  AND "date" < '2010-11-01'
  RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

 WITH 递归

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;