postgresql 中的 with 用法

时间：2019-04-17 15:59:18 收藏：0 阅读：144

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;

递归查询部门

原文：https://www.cnblogs.com/spring20190213dream/p/10609564.html