- 浏览: 357756 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (55)
- java基础 (18)
- oracle (13)
- PL/SQL (8)
- Hibernate (3)
- JDBC (1)
- JVM (1)
- Dump (1)
- windows (2)
- struts2 (2)
- SQL (3)
- OGNL (1)
- Web前端 (3)
- 开发工具 (4)
- weblogic (0)
- shell (0)
- linux (1)
- windows,项目实施 (0)
- eclipse (3)
- SVN (1)
- vmware (2)
- 虚拟机 (2)
- hadoop (0)
- Android (0)
- 大数据 (2)
- Jquery (3)
- js (5)
- js,ajax (4)
- CKeditor (1)
- HTML (1)
- mysql (0)
最新评论
-
alangxi:
非常详尽,非常感谢。
BigDecimal 使用方法详解 -
nich002:
内容很详尽。
BigDecimal 使用方法详解 -
nich002:
看着真蛋疼。
BigDecimal 使用方法详解 -
zhangyinhu8680:
jz20110918 写道想问问楼主,sqlserver的 S ...
Oracle SQL中实现indexOf和lastIndexOf功能,substr和instr用法 -
zhangyinhu8680:
数据库我只用过oracle,sqlServer我我没有接触过, ...
Oracle SQL中实现indexOf和lastIndexOf功能,substr和instr用法
oracle树查询又称为递归查询,是最常用的查询方法之一,下文对oracle树查询作了详细的阐述,希望可以让您对oracle树查询有更深的认识。
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
select * from tablename start with cond1
connect by cond2
where cond3;
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中COND1是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
COND2是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID,即本记录的父亲是上一条记录。
COND3是过滤条件,用于对返回的所有记录进行过滤。
数据库表结构
查询语句
--由子节点向根节点追溯
select *
from t_address t
start with t.address_name = '北京市'
connect by prior t.parent_id = t.id order by t.id asc;
--由根查询所有子节点
select * from t_address t start with t.address_name='天津市'
connect by t.parent_id= prior t.id
--由关键字address_level查询所在层次
select t.* ,t.address_level from t_address t start with t.address_name='海淀区'
connect by prior t.parent_id= t.id
======================================================================
---==========----================---===============----==============
----------------------------下边内容为收藏版本---------------------------
Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法,我们可以将一个表形结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等,在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。
以我做过的一个项目中的表为例,表结构如下:
Sql代码
1. CREATE TABLE FLFL
2. (
3. ID NUMBER NOT NULL,
4. MC NVARCHAR2(20),
5. FLJB NUMBER,
6. SJFLID NUMBER
7. )
CREATE TABLE FLFL
(
ID NUMBER NOT NULL,
MC NVARCHAR2(20),
FLJB NUMBER,
SJFLID NUMBER
)
FLJB是作为树的级别,在很多查询中可以加快SQL的查询效率。在下面演示的功能基本上不使用这个关键字。
SJFLID存储的是上级ID,如果是顶级父节点,该SJFLID为null(得补充一句,当初的确是这样设计的,不过现在知道,表中最好别有null记录,这会引起全文扫描,建议改成0代替)。
我们从最基本的操作,逐步列出树查询中常见的操作,所以查询出来的节点以家族中的辈份作比方。
1. 查找树中的所有顶级父节点(辈份最长的人)。 假设这个树是个目录结构,那么第一个操作总是找出所有的顶级节点,再根据该节点找到其下属节点。
Sql代码
1. SELECT * FROM flfl WHERE sjflid IS NULL;
SELECT * FROM flfl WHERE sjflid IS NULL;
这是个引子,没用到树型查询。
2.查找一个节点的直属子节点(所有儿子)。 如果查找的是直属子类节点,也是不用用到树型查询的。
Sql代码
1. SELECT * FROM flfl WHERE sjflid = 819459;
SELECT * FROM flfl WHERE sjflid = 819459;
这个可以找到ID为819459的直属子类节点。
3.查找一个节点的所有 直属子节点(所有后代)。
Sql代码
1. SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID;
SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID;
这个查找的是ID为819459的节点下的所有直属子类节点,包括子辈的和孙子辈的所有直属节点。
4.查找一个节点的直属父节点(父亲)。 如果查找的是节点的直属父节点,也是不用用到树型查询的。
Sql代码
1. SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758;
SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758;
这个找到的是ID为6758的节点的直属父节点,要用到同一张表的关联了。
5.查找一个节点的所有直属父节点(祖宗)。
Sql代码
1. SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;
SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;
这里查找的就是ID为6758的所有直属父节点,打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点,姑且认为是个倒序吧。
上面列出两个树型查询方式,第3条语句和第5条语句,这两条语句之间的区别在于prior关键字的位置不同,所以决定了查询的方式不同。 当sjflid = PRIOR ID时,数据库会根据当前的ID迭代出sjflid与该ID相同的记录,所以查询的结果是迭代出了所有的子类记录;而PRIOR ID = sjflid时,数据库会跟据当前的sjflid来迭代出与当前的sjflid相同的id的记录,所以查询出来的结果就是所有的父类结果。
以下是一系列针对树结构的更深层次的查询,这里的查询不一定是最优的查询方式,或许只是其中的一种实现而已。
6.查询一个节点的兄弟节点(亲兄弟)。
Sql代码
1. SELECT a.*
2. FROM flfl a
3. WHERE EXISTS (SELECT *
4. FROM flfl b
5. WHERE a.sjflid = b.sjflid AND b.ID = 6757);
SELECT a.*
FROM flfl a
WHERE EXISTS (SELECT *
FROM flfl b
WHERE a.sjflid = b.sjflid AND b.ID = 6757);
这里查询的就是与ID为6757的节点同属一个父节点的节点了,就好比亲兄弟了。
7.查询与一个节点同级的节点(族兄弟)。 如果在表中设置了级别的字段,上表中的FLJB,那么在做这类查询时会很轻松,同一级别的就是与那个节点同级的,在这里列出不使用该字段时的实现!
Sql代码
1. WITH tmp AS
2. (SELECT a.*, LEVEL lev
3. FROM flfl a
4. START WITH a.sjflid IS NULL
5. CONNECT BY a.sjflid = PRIOR a.ID)
6. SELECT *
7. FROM tmp
8. WHERE lev = (SELECT lev
9. FROM tmp
10. WHERE ID = 819394)
WITH tmp AS
(SELECT a.*, LEVEL lev
FROM flfl a
START WITH a.sjflid IS NULL
CONNECT BY a.sjflid = PRIOR a.ID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = 819394)
这里使用两个技巧,一个是使用了LEVEL来标识每个节点在表中的级别,还有就是使用with语法模拟出了一张带有级别的临时表。
8.查询一个节点的父节点的的兄弟节点(伯父与叔父)。
Sql代码
1. WITH tmp AS
2. (SELECT flfl.*, LEVEL lev
3. FROM flfl
4. START WITH sjflid IS NULL
5. CONNECT BY sjflid = PRIOR ID)
6. SELECT b.*
7. FROM tmp b,
8. (SELECT *
9. FROM tmp
10. WHERE ID = 7004 AND lev = 2) a
11. WHERE b.lev = 1
12. UNION ALL
13. SELECT *
14. FROM tmp
15. WHERE sjflid = (SELECT DISTINCT x.ID
16. FROM tmp x,
17. tmp y,
18. (SELECT *
19. FROM tmp
20. WHERE ID = 7004 AND lev > 2) z
21. WHERE y.ID = z.sjflid AND x.ID = y.sjflid);
WITH tmp AS
(SELECT flfl.*, LEVEL lev
FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID)
SELECT b.*
FROM tmp b,
(SELECT *
FROM tmp
WHERE ID = 7004 AND lev = 2) a
WHERE b.lev = 1
UNION ALL
SELECT *
FROM tmp
WHERE sjflid = (SELECT DISTINCT x.ID
FROM tmp x,
tmp y,
(SELECT *
FROM tmp
WHERE ID = 7004 AND lev > 2) z
WHERE y.ID = z.sjflid AND x.ID = y.sjflid);
这里查询分成以下几步。首先,将第7个一样,将全表都使用临时表加上级别;其次,根据级别来判断有几种类型,以上文中举的例子来说,有三种情况:(1)当前节点为顶级节点,即查询出来的lev值为1,那么它没有上级节点,不予考虑。(2)当前节点为2级节点,查询出来的lev值为2,那么就只要保证lev级别为1的就是其上级节点的兄弟节点。(3)其它情况就是3以及以上级别,那么就要选查询出来其上级的上级节点(祖父),再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。 最后,就是使用UNION将查询出来的结果进行结合起来,形成结果集。
9.查询一个节点的父节点的同级节点(族叔)。
这个其实跟第7种情况是相同的。
Sql代码
1. WITH tmp AS
2. (SELECT a.*, LEVEL lev
3. FROM flfl a
4. START WITH a.sjflid IS NULL
5. CONNECT BY a.sjflid = PRIOR a.ID)
6. SELECT *
7. FROM tmp
8. WHERE lev = (SELECT lev
9. FROM tmp
10. WHERE ID = 819394) - 1
WITH tmp AS
(SELECT a.*, LEVEL lev
FROM flfl a
START WITH a.sjflid IS NULL
CONNECT BY a.sjflid = PRIOR a.ID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = 819394) - 1
只需要做个级别判断就成了。
基本上,常见的查询在里面了,不常见的也有部分了。其中,查询的内容都是节点的基本信息,都是数据表中的基本字段,但是在树查询中还有些特殊需求,是对查询数据进行了处理的,常见的包括列出树路径等。
补充一个概念,对于数据库来说,根节点并不一定是在数据库中设计的顶级节点,对于数据库来说,根节点就是start with开始的地方。
下面列出的是一些与树相关的特殊需求。
10.名称要列出名称全部路径。
这里常见的有两种情况,一种是是从顶级列出,直到当前节点的名称(或者其它属性);一种是从当前节点列出,直到顶级节点的名称(或其它属性)。举地址为例:国内的习惯是从省开始、到市、到县、到居委会的,而国外的习惯正好相反(老师说的,还没接过国外的邮件,谁能寄个瞅瞅 )。
从顶部开始:
Sql代码
1. SELECT SYS_CONNECT_BY_PATH (mc, '/')
2. FROM flfl
3. WHERE ID = 6498
4. START WITH sjflid IS NULL
5. CONNECT BY sjflid = PRIOR ID;
SELECT SYS_CONNECT_BY_PATH (mc, '/')
FROM flfl
WHERE ID = 6498
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;
从当前节点开始:
Sql代码
1. SELECT SYS_CONNECT_BY_PATH (mc, '/')
2. FROM flfl
3. START WITH ID = 6498
4. CONNECT BY PRIOR sjflid = ID;
SELECT SYS_CONNECT_BY_PATH (mc, '/')
FROM flfl
START WITH ID = 6498
CONNECT BY PRIOR sjflid = ID;
在这里我又不得不放个牢骚了。oracle只提供了一个sys_connect_by_path函数,却忘了字符串的连接的顺序。在上面的例子中,第一个SQL是从根节点开始遍历,而第二个SQL是直接找到当前节点,从效率上来说已经是千差万别,更关键的是第一个SQL只能选择一个节点,而第二个SQL却是遍历出了一颗树来。再次PS一下。
sys_connect_by_path函数就是从start with开始的地方开始遍历,并记下其遍历到的节点,start with开始的地方被视为根节点,将遍历到的路径根据函数中的分隔符,组成一个新的字符串,这个功能还是很强大的。
11.列出当前节点的根节点。
在前面说过,根节点就是start with开始的地方。
Sql代码
1. SELECT CONNECT_BY_ROOT mc, flfl.*
2. FROM flfl
3. START WITH ID = 6498
4. CONNECT BY PRIOR sjflid = ID;
SELECT CONNECT_BY_ROOT mc, flfl.*
FROM flfl
START WITH ID = 6498
CONNECT BY PRIOR sjflid = ID;
connect_by_root函数用来列的前面,记录的是当前节点的根节点的内容。
12.列出当前节点是否为叶子。
这个比较常见,尤其在动态目录中,在查出的内容是否还有下级节点时,这个函数是很适用的。
Sql代码
1. SELECT CONNECT_BY_ISLEAF, flfl.*
2. FROM flfl
3. START WITH sjflid IS NULL
4. CONNECT BY sjflid = PRIOR ID;
SELECT CONNECT_BY_ISLEAF, flfl.*
FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;
connect_by_isleaf函数用来判断当前节点是否包含下级节点,如果包含的话,说明不是叶子节点,这里返回0;反之,如果不包含下级节点,这里返回1。
至此,oracle树型查询基本上讲完了,以上的例子中的数据是使用到做过的项目中的数据,因为里面的内容可能不好理解,所以就全部用一些新的例子来进行阐述。以上所有SQL都在本机上测试通过,也都能实现相应的功能,但是并不能保证是解决这类问题的最优方案(如第8条明显写成存储过程会更好),如果谁有更好的解决方案、或者有关oracle树查询的任何问题,欢迎留言讨论,以上的SQL有什么问题也欢迎大家留言批评。
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
select * from tablename start with cond1
connect by cond2
where cond3;
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中COND1是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
COND2是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID,即本记录的父亲是上一条记录。
COND3是过滤条件,用于对返回的所有记录进行过滤。
数据库表结构
查询语句
--由子节点向根节点追溯
select *
from t_address t
start with t.address_name = '北京市'
connect by prior t.parent_id = t.id order by t.id asc;
--由根查询所有子节点
select * from t_address t start with t.address_name='天津市'
connect by t.parent_id= prior t.id
--由关键字address_level查询所在层次
select t.* ,t.address_level from t_address t start with t.address_name='海淀区'
connect by prior t.parent_id= t.id
======================================================================
---==========----================---===============----==============
----------------------------下边内容为收藏版本---------------------------
Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法,我们可以将一个表形结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等,在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。
以我做过的一个项目中的表为例,表结构如下:
Sql代码
1. CREATE TABLE FLFL
2. (
3. ID NUMBER NOT NULL,
4. MC NVARCHAR2(20),
5. FLJB NUMBER,
6. SJFLID NUMBER
7. )
CREATE TABLE FLFL
(
ID NUMBER NOT NULL,
MC NVARCHAR2(20),
FLJB NUMBER,
SJFLID NUMBER
)
FLJB是作为树的级别,在很多查询中可以加快SQL的查询效率。在下面演示的功能基本上不使用这个关键字。
SJFLID存储的是上级ID,如果是顶级父节点,该SJFLID为null(得补充一句,当初的确是这样设计的,不过现在知道,表中最好别有null记录,这会引起全文扫描,建议改成0代替)。
我们从最基本的操作,逐步列出树查询中常见的操作,所以查询出来的节点以家族中的辈份作比方。
1. 查找树中的所有顶级父节点(辈份最长的人)。 假设这个树是个目录结构,那么第一个操作总是找出所有的顶级节点,再根据该节点找到其下属节点。
Sql代码
1. SELECT * FROM flfl WHERE sjflid IS NULL;
SELECT * FROM flfl WHERE sjflid IS NULL;
这是个引子,没用到树型查询。
2.查找一个节点的直属子节点(所有儿子)。 如果查找的是直属子类节点,也是不用用到树型查询的。
Sql代码
1. SELECT * FROM flfl WHERE sjflid = 819459;
SELECT * FROM flfl WHERE sjflid = 819459;
这个可以找到ID为819459的直属子类节点。
3.查找一个节点的所有 直属子节点(所有后代)。
Sql代码
1. SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID;
SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID;
这个查找的是ID为819459的节点下的所有直属子类节点,包括子辈的和孙子辈的所有直属节点。
4.查找一个节点的直属父节点(父亲)。 如果查找的是节点的直属父节点,也是不用用到树型查询的。
Sql代码
1. SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758;
SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758;
这个找到的是ID为6758的节点的直属父节点,要用到同一张表的关联了。
5.查找一个节点的所有直属父节点(祖宗)。
Sql代码
1. SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;
SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;
这里查找的就是ID为6758的所有直属父节点,打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点,姑且认为是个倒序吧。
上面列出两个树型查询方式,第3条语句和第5条语句,这两条语句之间的区别在于prior关键字的位置不同,所以决定了查询的方式不同。 当sjflid = PRIOR ID时,数据库会根据当前的ID迭代出sjflid与该ID相同的记录,所以查询的结果是迭代出了所有的子类记录;而PRIOR ID = sjflid时,数据库会跟据当前的sjflid来迭代出与当前的sjflid相同的id的记录,所以查询出来的结果就是所有的父类结果。
以下是一系列针对树结构的更深层次的查询,这里的查询不一定是最优的查询方式,或许只是其中的一种实现而已。
6.查询一个节点的兄弟节点(亲兄弟)。
Sql代码
1. SELECT a.*
2. FROM flfl a
3. WHERE EXISTS (SELECT *
4. FROM flfl b
5. WHERE a.sjflid = b.sjflid AND b.ID = 6757);
SELECT a.*
FROM flfl a
WHERE EXISTS (SELECT *
FROM flfl b
WHERE a.sjflid = b.sjflid AND b.ID = 6757);
这里查询的就是与ID为6757的节点同属一个父节点的节点了,就好比亲兄弟了。
7.查询与一个节点同级的节点(族兄弟)。 如果在表中设置了级别的字段,上表中的FLJB,那么在做这类查询时会很轻松,同一级别的就是与那个节点同级的,在这里列出不使用该字段时的实现!
Sql代码
1. WITH tmp AS
2. (SELECT a.*, LEVEL lev
3. FROM flfl a
4. START WITH a.sjflid IS NULL
5. CONNECT BY a.sjflid = PRIOR a.ID)
6. SELECT *
7. FROM tmp
8. WHERE lev = (SELECT lev
9. FROM tmp
10. WHERE ID = 819394)
WITH tmp AS
(SELECT a.*, LEVEL lev
FROM flfl a
START WITH a.sjflid IS NULL
CONNECT BY a.sjflid = PRIOR a.ID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = 819394)
这里使用两个技巧,一个是使用了LEVEL来标识每个节点在表中的级别,还有就是使用with语法模拟出了一张带有级别的临时表。
8.查询一个节点的父节点的的兄弟节点(伯父与叔父)。
Sql代码
1. WITH tmp AS
2. (SELECT flfl.*, LEVEL lev
3. FROM flfl
4. START WITH sjflid IS NULL
5. CONNECT BY sjflid = PRIOR ID)
6. SELECT b.*
7. FROM tmp b,
8. (SELECT *
9. FROM tmp
10. WHERE ID = 7004 AND lev = 2) a
11. WHERE b.lev = 1
12. UNION ALL
13. SELECT *
14. FROM tmp
15. WHERE sjflid = (SELECT DISTINCT x.ID
16. FROM tmp x,
17. tmp y,
18. (SELECT *
19. FROM tmp
20. WHERE ID = 7004 AND lev > 2) z
21. WHERE y.ID = z.sjflid AND x.ID = y.sjflid);
WITH tmp AS
(SELECT flfl.*, LEVEL lev
FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID)
SELECT b.*
FROM tmp b,
(SELECT *
FROM tmp
WHERE ID = 7004 AND lev = 2) a
WHERE b.lev = 1
UNION ALL
SELECT *
FROM tmp
WHERE sjflid = (SELECT DISTINCT x.ID
FROM tmp x,
tmp y,
(SELECT *
FROM tmp
WHERE ID = 7004 AND lev > 2) z
WHERE y.ID = z.sjflid AND x.ID = y.sjflid);
这里查询分成以下几步。首先,将第7个一样,将全表都使用临时表加上级别;其次,根据级别来判断有几种类型,以上文中举的例子来说,有三种情况:(1)当前节点为顶级节点,即查询出来的lev值为1,那么它没有上级节点,不予考虑。(2)当前节点为2级节点,查询出来的lev值为2,那么就只要保证lev级别为1的就是其上级节点的兄弟节点。(3)其它情况就是3以及以上级别,那么就要选查询出来其上级的上级节点(祖父),再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。 最后,就是使用UNION将查询出来的结果进行结合起来,形成结果集。
9.查询一个节点的父节点的同级节点(族叔)。
这个其实跟第7种情况是相同的。
Sql代码
1. WITH tmp AS
2. (SELECT a.*, LEVEL lev
3. FROM flfl a
4. START WITH a.sjflid IS NULL
5. CONNECT BY a.sjflid = PRIOR a.ID)
6. SELECT *
7. FROM tmp
8. WHERE lev = (SELECT lev
9. FROM tmp
10. WHERE ID = 819394) - 1
WITH tmp AS
(SELECT a.*, LEVEL lev
FROM flfl a
START WITH a.sjflid IS NULL
CONNECT BY a.sjflid = PRIOR a.ID)
SELECT *
FROM tmp
WHERE lev = (SELECT lev
FROM tmp
WHERE ID = 819394) - 1
只需要做个级别判断就成了。
基本上,常见的查询在里面了,不常见的也有部分了。其中,查询的内容都是节点的基本信息,都是数据表中的基本字段,但是在树查询中还有些特殊需求,是对查询数据进行了处理的,常见的包括列出树路径等。
补充一个概念,对于数据库来说,根节点并不一定是在数据库中设计的顶级节点,对于数据库来说,根节点就是start with开始的地方。
下面列出的是一些与树相关的特殊需求。
10.名称要列出名称全部路径。
这里常见的有两种情况,一种是是从顶级列出,直到当前节点的名称(或者其它属性);一种是从当前节点列出,直到顶级节点的名称(或其它属性)。举地址为例:国内的习惯是从省开始、到市、到县、到居委会的,而国外的习惯正好相反(老师说的,还没接过国外的邮件,谁能寄个瞅瞅 )。
从顶部开始:
Sql代码
1. SELECT SYS_CONNECT_BY_PATH (mc, '/')
2. FROM flfl
3. WHERE ID = 6498
4. START WITH sjflid IS NULL
5. CONNECT BY sjflid = PRIOR ID;
SELECT SYS_CONNECT_BY_PATH (mc, '/')
FROM flfl
WHERE ID = 6498
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;
从当前节点开始:
Sql代码
1. SELECT SYS_CONNECT_BY_PATH (mc, '/')
2. FROM flfl
3. START WITH ID = 6498
4. CONNECT BY PRIOR sjflid = ID;
SELECT SYS_CONNECT_BY_PATH (mc, '/')
FROM flfl
START WITH ID = 6498
CONNECT BY PRIOR sjflid = ID;
在这里我又不得不放个牢骚了。oracle只提供了一个sys_connect_by_path函数,却忘了字符串的连接的顺序。在上面的例子中,第一个SQL是从根节点开始遍历,而第二个SQL是直接找到当前节点,从效率上来说已经是千差万别,更关键的是第一个SQL只能选择一个节点,而第二个SQL却是遍历出了一颗树来。再次PS一下。
sys_connect_by_path函数就是从start with开始的地方开始遍历,并记下其遍历到的节点,start with开始的地方被视为根节点,将遍历到的路径根据函数中的分隔符,组成一个新的字符串,这个功能还是很强大的。
11.列出当前节点的根节点。
在前面说过,根节点就是start with开始的地方。
Sql代码
1. SELECT CONNECT_BY_ROOT mc, flfl.*
2. FROM flfl
3. START WITH ID = 6498
4. CONNECT BY PRIOR sjflid = ID;
SELECT CONNECT_BY_ROOT mc, flfl.*
FROM flfl
START WITH ID = 6498
CONNECT BY PRIOR sjflid = ID;
connect_by_root函数用来列的前面,记录的是当前节点的根节点的内容。
12.列出当前节点是否为叶子。
这个比较常见,尤其在动态目录中,在查出的内容是否还有下级节点时,这个函数是很适用的。
Sql代码
1. SELECT CONNECT_BY_ISLEAF, flfl.*
2. FROM flfl
3. START WITH sjflid IS NULL
4. CONNECT BY sjflid = PRIOR ID;
SELECT CONNECT_BY_ISLEAF, flfl.*
FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;
connect_by_isleaf函数用来判断当前节点是否包含下级节点,如果包含的话,说明不是叶子节点,这里返回0;反之,如果不包含下级节点,这里返回1。
至此,oracle树型查询基本上讲完了,以上的例子中的数据是使用到做过的项目中的数据,因为里面的内容可能不好理解,所以就全部用一些新的例子来进行阐述。以上所有SQL都在本机上测试通过,也都能实现相应的功能,但是并不能保证是解决这类问题的最优方案(如第8条明显写成存储过程会更好),如果谁有更好的解决方案、或者有关oracle树查询的任何问题,欢迎留言讨论,以上的SQL有什么问题也欢迎大家留言批评。
发表评论
-
Oracle的rollup、cube、grouping sets函数
2015-06-17 11:22 4916文章转自 http://blog.csdn.net/huan ... -
Oracle的Replace函数与translate函数详解与比较
2014-12-10 14:12 1277Oracle的Replace函数与translate函数详解与 ... -
hibernate通过映射文件生成表结构
2013-05-12 14:38 0文章来自 http://dianziermu. ... -
ORA-01219 :数据库未打开:仅允许在固定表/视图中查询 解决!
2013-01-26 01:01 17955ORA-01219:database not open:que ... -
Oracle同义词
2012-12-14 16:46 0Oracle同义词的创建与删除(2012-03-20 22:1 ... -
exp 用户名/密码@数据库名
2012-10-28 13:07 0在oracle中的bin目录下 exp ... -
恢复到update或者delete之前的数据(非常重要)
2012-10-22 20:28 0首先要开启ROW MOVEMENT功能 SQL>ALTE ... -
函数Ratio_to_reportde 的使用方法(举例说明)
2012-10-08 18:45 0分析函数Ratio_to_report( ) over()使用 ... -
Oracle中的MINUS和UNION用法(转)
2012-10-23 11:26 7935Oracle中的MINUS和UNION用法(转)Oracle中 ... -
Oracle 表删除恢复
2012-07-02 13:36 1966在Oracle 10g数据库中,引入了一个回收站(Recycl ... -
Oracle Connect By用法
2012-06-26 16:58 0oracle中的select语句可以 ... -
SQL 在常用报表业务中的归并、转换与信息汇总中的应用技巧
2012-06-06 22:24 1791本文阐述并分步骤汇总了 SQL 数据库报表系统中最常见的三类典 ... -
Oracle SQL中实现indexOf和lastIndexOf功能,substr和instr用法
2012-05-29 13:08 30342Oracle SQL中实现indexOf和lastIndexO ... -
oracle SELECT INTO 和 INSERT INTO SELECT 区别
2012-05-10 13:16 3199我们经常会遇到需要表复制的情况,如将一个table1的数据的部 ... -
oracle数据库函数
2012-05-04 21:24 21861字符函数 length函数 select length(' ... -
如何一条SQL快速生成100万条测试数据 .
2012-04-22 11:09 3640创建大量的测试数据,动不动就需要上万条,如何通过一条SQL快速 ... -
oracle sql语句优化
2012-04-22 11:02 1374oracle sql语句优化(转载) (1) ... -
decode函数使用
2012-04-22 11:02 1221DECODE函数,是ORACLE公司的SQL软件ORACLE ...
相关推荐
oracle树状结构查询即层次递归查询,是sql语句经常用到的,在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。这篇文章给大家介绍了Oracle递归树形结构查询功能,需要的朋友参考下
递归查询菜单树,支持mysql,oracle,含表结构,递归查询菜单,支持mysql,oracle,含表结构递归查询菜单。
Oracle使用递归查询。查询树结构的sql。在Oracle中,递归查询要用到start with ……connect by prior……
oracle 如何递归查询父子关系。经常用于构造树结构
Oracle使用递归SQL展示一棵树
有的情况下,我们需要用递归的方法整理数据,这才程序中很容易做到,但是在数据库 ...下面我以最典型的树形结构来说明下如何在Oracle使用递归查 询。 为了说明方便,创建一张数据库表,用于存储一个简单的树形结构
Oracle中的树状查询(递归查询),权限查询,树形结构
主要给大家介绍了关于Oracle如何通过递归查询父子兄弟节点的相关资料,递归查询对各位程序员来说应该都不陌生,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧。
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
完美解决多级递归查询,支持多种数据库,可以参照类似写法。有的数据库还是不支持的,但有类似的写法如oracle
在ORACLE、MSSQL、MYSQL中树结构表递归查询的实现.pdf
NULL 博文链接:https://flysky7931.iteye.com/blog/706171
前言 ...Oracle递归查询 oracle实现递归查询的话,就可以使用start with … connect by connect by递归查询基本语法是: select 1 from 表格 start with … connect by prior id = pId start with
用mysql实现oracle递归,通过with as 来实现
Oracle的递归查询收集的几条在oracle中通过connectbyprior来实现递归查询 Startwith...ConnectBy子句递归查询一般用于一个表维护树形结构的应用。 创建示例表:CREATETABLETBL_TEST(IDNUMBER,NAMEVARCHAR2(100BYTE),...
内含很多oracle的语法和常用函数,包括 对于oracle进行简单树查询(递归查询),oracle内置函数大全, PLSQL 编程手册(SQL大全),及分页建表等很多学习资料。
本文介绍Oracle中使用START WITH...CONNECT BY PRIOR子句实现递归查询树形结构的方法,小伙伴们可以参考一下。
在不引入MPTT模型的前提下,必须通过递归算法来查询某个节点和下级子节点。 Oracle提供的connect by扩展语法,简单好用。但是其他的RDBMS就没这么人性化了(或者我不知道)。最近在项目中使用PostgreSQL来查询树形...