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
以上:向上递归查询部门