postgresql with的j简单使用

with的使用

可以看出来with相当于一个临时表，如下示例表明with语句ename_emp将emp的empno,ename两个字段作为一个临时表进行使用
mydb=# with ename_tmp as (select empno,ename from emp)
mydb-# select * from ename_tmp;
empno | ename
-------+--------
7369 | SMITH
7499 | ALLEN
7521 | WARD
7566 | JONES
7654 | MARTIN
7698 | BLAKE
7782 | CLARK
7788 | SCOTT
7839 | KING
7844 | TURNER
7876 | ADAMS
7900 | JAMES
7902 | FORD
7934 | MILLER
(14 rows)

mydb=#

with加上RECURSIVE ，则表示递归的意思

WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)
SELECT sum(n) FROM t;
mydb=# WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)
mydb-# SELECT sum(n) FROM t;
sum

5050
(1 row)

mydb=#

with RECURSIVE t as
(select * from template_info where id =18
union all
(select d.* from t inner join template_info d on t.id=d.parent_id)
) select * from t
将第一个非递归子句进行output,output到一个临时表中

2.读取递归子句，判断temp working table中是否有数据，如果有进行读取，即目前读到的是第一个非递归子句即select * from template_info where id =18的执行结果，将结果输出到中间表，同时输出到temp intermediate table中，并且替换掉temp working table中的数据(替换过程我个人理解在这个语句中就是第一个非递归子句和第一次递归子句执行的结果进行union后，清楚临时工作表，然后在将union后的结果输出后，在输入到临时工作表中，然后在进行下一次的递归过程)
3.一直循环递归，一直到where子句中的条件不匹配，跳出递归流程

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;

这个查询实际上将products中的行移动到products_log。WITH中的DELETE删除来自products的指定行，以RETURNING子句返回被删除的内容，然后主查询读该输出并将它插入到products_log。

//with相当于创建临时表之后缓存结果，然后使用匹配临时表结果去匹配表中查询的条件
with limit_tbl as (
select id,name from test_tbl limit 2
),limit_tbl2 as (
select id,cerate_time from test_tbl limit 2
)
select test_tbl.id,test_tbl.name from test_tbl where id in (select id from limit_tbl union all select id from limit_tbl2);

//with创建临时表结果缓存后，select直接查询临时结果
with limit_tbl as (
select id,name from test_tbl limit 2
),limit_tbl2 as (
select id,cerate_time from test_tbl limit 2
)
select a.id,a.name,b.cerate_time from limit_tbl a,limit_tbl2 b where a.id=b.id;