Start with...Connect By
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。(即递归查询)
一个使用实例,创建示例表:
CREATE TABLE TBL_TEST
(ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0);
插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归 (取全部“子孙”集):
SELECT * FROM TBL_TEST
start WITH id=1
connect BY prior id = pid
ORDER BY pid
从末梢往树ROOT递归(取全部“父爷”集):
SELECT * FROM TBL_TEST
start WITH id=5
connect BY prior pid = id
ORDER BY id
分享到:
相关推荐
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
本文章详细介绍了Oracle中connect by...start with...的用法。
Oracle start with.connect by prior子句实现递归查询
在Oracle中用Start with...Connect By子句递归查询
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Oracle row_number()over start with...connect by prior start with...connect by prior
Oracle 数据库树形结构用法总结,例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍
使用 START WITH 和 CONNECT BY 子句可以实现 SQL 的层次查询,并且可以使用 SYS_CONNECT_BY_PATH 函数和 CONNECT_BY_ROOT、CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE 等伪列函数来增强层次查询的能力。
其中,`START WITH` 子句指定了递归查询的起始点,`CONNECT BY` 子句指定了递归查询的连接条件。 在上面的示例代码中,我们创建了一个名为 `T_DEPT_HP` 的表,并插入了一些示例数据。然后,我们可以使用递归函数来...
Successfully design and simulate your 3D robot model and use powerful robotics algorithms and tools to program and set up your robots with an unparalleled experience by using the exciting new features...
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
oracle中的数查询,介绍的详细,有例子。
Oracle常用的sql语句文件以及说明(doc、txt)文件 例如:analyze分析表、oracle 常用表和视图、Select Start with ... Connect by 分层次输出、SQL语句优化、函数的使用和说明。
Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
Oracle_start_with_connect_by_prior_用法[文].pdf
A practical guide with clear instructions to design and develop a complete web application from start to finish Who This Book Is For This book is designed for JavaScript developers of any skill level ...
In a world where understanding big data has become key, by mastering R you will be able to deal with your data effectively and efficiently. This book will give you the guidance you need to build and...
Source Code for Hello World with Pygame ................................................................................ 7 Setting Up a Pygame Program ....................................................