概述
with 子句是在oracle 9i release 2 中引入的。with 子句又叫做子查询构造语句。可以用来给一个子查询块命名。
该查询块的检索结果会被oracle 保存在用户的临时表空间中，该命名块就像虚表或者内联视图一样。
语法
WITH subquery_name AS (the aggregation SQL statement) SELECT (query naming subquery_name);
With查询语句不是以select开始的，而是以“WITH”关键字开头。
可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理
优点
使用SQL with 子句的优点，
- 增加了SQL的易读性，如果构造了多个子查询，结构会更清晰
- 更重要的是：“一次分析，多次使用”。当在一个sql 语句中需要多次使用到某个子查询的结果的时候，可以提升sql 语句的性能。 这也是为什么会提供性能的地方，达到了“少读”的目标。
栗子
取 5到10的数据，通常我们写如下sql:
select * from ( select level as lv from dual connect by level < 10 ) tt where tt.lv between 5 and 10 ;
现在改为 使用With as 的SQL：
with tt as ( select level as lv from dual connect by level <10 ) select tt.lv from tt where tt.lv between 5 and 10 ;
实战
查询系统菜单的后台sql
WITH TEMPVIEW AS (SELECT B.MENU_ID AS PARTY_ID, '1' AS TYPE, B.DIR_ID AS PARENT_ID, A.PRIV_NAME AS PARTY_NAME, A.URL, A.IS_HOLD, A.IS_AUTHORIZED FROM BFM_PRIV A, BFM_MENU_DIR B WHERE A.PRIV_ID = B.MENU_ID AND A.STATE = 'A' AND B.DIR_ID IN (SELECT DIR_ID FROM BFM_DIR START WITH DIR_ID = :1 --绑定变量 CONNECT BY PRIOR DIR_ID = PARENT_ID AND STATE = 'A') UNION ALL SELECT C.DIR_ID AS PARTY_ID, '0' AS TYPE, C.PARENT_ID, C.DIR_NAME AS PARTY_NAME, NULL, NULL, NULL FROM BFM_DIR C START WITH DIR_ID = :2 --绑定变量 CONNECT BY PRIOR DIR_ID = PARENT_ID AND STATE = 'A') SELECT * FROM TEMPVIEW START WITH PARTY_ID = :3 --绑定变量 CONNECT BY PRIOR PARTY_ID = PARENT_ID ORDER SIBLINGS BY PARTY_ID ;
ORDER SIBLINGS BY的意思就是在兄弟姐妹之间的排序，和order by所表示的含义绝对不同，针对树状sql，我觉得ORDER SIBLINGS BY更有意义，树状sql查询出来的结果本身就是按照层次（hierarchy）结构排好序的，而加上关键字SIBLINGS 其实是在hierarchy内部进行排序。
在层次查询中如需对亲兄弟的先后顺序进行限定，就必须要使用特有的“ORDER SIBLINGS BY”而非“ORDER BY”子句。切记之。
