ORACLE-WITH 子句详解
概述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 子句的优点,
栗子取 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 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”子句。切记之。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |