Oracle高阶教程之 connect by实例
程序员文章站
2023-08-31 20:37:11
oracle高阶之 connect by
create table t2(
root_id number,
id number,
name varchar(5),...
oracle高阶之 connect by
create table t2( root_id number, id number, name varchar(5), description varchar(10) );
insert into t2(root_id,id,name,description)values(0,1,'a','aaa'); insert into t2(root_id,id,name,description)values(1,2,'a1','aaa1'); insert into t2(root_id,id,name,description)values(1,3,'a2','aaa2'); insert into t2(root_id,id,name,description)values(0,4,'b','bbb'); insert into t2(root_id,id,name,description)values(4,5,'b1','bbb1'); insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2')
select * from t2;
--prior在哪边,就表示哪列为父列 select * from t2 start with root_id = 0 connect by prior id = root_id; --level伪列表示树的深度(或叫高度)。 select t2.*,level from t2 connect by prior id = root_id; --首先,原表中id为1-6的6行数据均为第一层(id为父列),level=1 --其次,root_id的值等于id列的行为第二层,level=2 --所以查询结果共9行 select t2.*,level from t2 start with root_id = 0 connect by prior id = root_id; --start with子句限定了第一层数据,即,仅筛选root_id=0的记录 --<<当表中有重复行时,这些重复行不会合并在一起,而是单独算的 insert into t2(root_id,id,name,description)values(4,6,'b2','bbb2'); commit; select t2.*,level from t2 connect by prior id = root_id order by level,id; -->> --获取特定子树 select t2.*,level from t2 start with id = 4 connect by prior id = root_id; --prior被省略,则查询将不进行深层递归 select * from t2 start with root_id = 0 connect by id = root_id; select t2.*,level from t2 connect by level<3;
上一篇: 清远黄腾峡漂流好玩吗 黄腾峡漂流游玩测评
下一篇: 笑话很简单只要一句话
推荐阅读
-
Oracle高阶教程之 connect by实例
-
oracle进阶教程之connectby实例学习
-
oracle教程之Oracle的多表查询举例(笛卡尔集、等值与非等值连接、外连接、自连接、SQL1999连接、Oracle的set运算符)实例
-
Oracle入门教程之数据库实例管理
-
Oracle高阶教程之 connect by实例
-
oracle进阶教程之connectby实例学习
-
Oracle入门教程之数据库实例管理
-
Oracle存储过程之数据库中获取数据实例_Oracle应用_脚本之家
-
Oracle存储过程之数据库中获取数据实例_Oracle应用_脚本之家
-
oracle教程之Oracle的多表查询举例(笛卡尔集、等值与非等值连接、外连接、自连接、SQL1999连接、Oracle的set运算符)实例