请教start with.....connect by子句的用法!!
多谢了!!
多谢了!!
解决方案 »
- oracle连接远程数据库ORA-12541: TNS: 无监听程序,本地没问题
- 使用connect by时如何对level进行限制?
- oracle 导出 ACCESS的语句 急用
- create index探微
- 使用同义词和dblink,插入表时提示此操作的global_names参数必须设置为TRUE
- ORACEL怎么查数据库的用户占用的进程和相对应的SQL语言?
- oracle 中fetch first怎么用
- 我的exp命令执行后,报有TNS错
- 有关 企业管理器(oracle Enterprise Manager)
- orcle生成XML节点问题-不能生成3级以后的节点
- 求sql语句
- 急助:触发器和存储过程碰上的钉子,搞了一天了,大虾们相助啊!!
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];WHERE condition:
expr comparison_operator exprKeywords and Clauses
Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses.
In the syntax:
SELECT Is the standard SELECT clause.
LEVEL For each row returned by a hierarchical query, the LEVELpseudocolumn
returns 1 for a root row, 2 for a child of a root, and so on.
FROM table Specifies the table, view, or snapshot containing the columns. You can
select from only one table.
WHERE Restricts the rows returned by the query without affecting other rows of
the hierarchy.
condition Is a comparison with expressions.
START WITH Specifies the root rows of the hierarchy (where to start). This clause is
required for a true hierarchical query.
CONNECT BY Specifies the columns in which the relationship between parent a nd child
PRIOR rows exist. This clause is required for a hierarchical query. The SELECT statement cannot contain a join or query from a view that contains a join. Here is an example:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id;
-------- ----- --------------------
中国 3
北京 4 中国
四川 3 中国
昌平 7 北京
顺义 12 北京
绵阳 20 四川
德阳 12 四川
SELECT SYS_CONNECT_BY_PATH(REGION, '/') ""Path""
FROM b
START WITH PARTOF is null
CONNECT BY PRIOR REGION = PARTOF
Path
------------------------
/中国
/中国/北京
/中国/北京/昌平
/中国/北京/顺义
/中国/四川
/中国/四川/绵阳
/中国/四川/德阳