postgresql 中的 with 用法

1 with 可以替代连接  让sql 更简洁 逻辑更清晰

2 with 语句备份要删除的语句

WITH delete_rows AS ( DELETE FROM company WHERE salary < 10000 RETURNING * ) INSERT INTO company1 ( SELECT * FROM delete_rows );

　　注意：不要忘记加上returning *    company1 表中必须 包含 company 中的字段

WITH RECURSIVE T AS (
    SELECT ID
        ,
        NAME,
        parent_id 
    FROM
        "f_department_2WIQRCZ" 
    WHERE
        ID = 2 UNION ALL
    SELECT K
        .ID,
        K.NAME,
        K.parent_id 
    FROM
        "f_department_2WIQRCZ" K,
    T 
    WHERE
        T.ID = K.parent_id 
    ) SELECT
    * 
FROM
    T;

以上：向下递归查询部门

with recursive T AS 
(
    select id,name,parent_id from "f_department_2WIQRCZ" where id = 10  --起始节点
    union all
    select  K.id, K.name, K.parent_id from "f_department_2WIQRCZ" K , T where K.ID = T.parent_id
)
select * from T

以上：向上递归查询部门

转载于:https://www.cnblogs.com/spring20190213dream/p/10609564.html