注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

星期五

明天不上班

 
 
 

日志

 
 
关于我

一个特立独行的Java程序员,比较宅,上上网,写博客,听音乐,看电影。

网易考拉推荐

Oracle高级查询之等级查询  

2011-02-12 20:15:00|  分类: oracle plsql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

Hierarchical Queries (等级查询)
你可能经常会遇到一些实际问题,例如家族树,公司职员之间的关系等
举一个例子

SQL  Statement

--------------------------------------------------------------------------------------------------------------------

DROP TABLE TESTTREE;

CREATE TABLE TESTTREE (   
      "ID" VARCHAR2(20 BYTE)  CONSTRAINT TESTTREE_pk PRIMARY KEY,
      "FID" VARCHAR2(20 BYTE) CONSTRAINT TESTTREE_fk REFERENCES TESTTREE(id),
      "NAME" VARCHAR2(20 BYTE),
      "TYPE" VARCHAR2(20 BYTE)
);
 
INSERT INTO "TESTTREE" (ID, NAME, TYPE) VALUES ('1', 'r1', 'r');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('2', '1', 's1', 's');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('3', '1', 's2', 's');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('4', '2', 'p1', 'pc');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('5', '2', 'p2', 'pc');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('6', '2', 'p3', 'pc');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('7', '3', 'p4', 'pc');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('8', '3', 's4', 's');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('9', '8', 's5', 's');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('10', '3', 'p5', 'pc');
INSERT INTO "TESTTREE" (ID, FID, NAME, TYPE) VALUES ('11', '8', 'p6', 'pc');

SELECT * FROM TESTTREE;
-------------------------------------------------------------------------------------------------------------------

查询结果:
ID                   FID                  NAME                 TYPE                
-------------------- -------------------- -------------------- --------------------
1                                         r1                   r                   
2                    1                    s1                   s                   
3                    1                    s2                   s                   
4                    2                    p1                   pc                  
5                    2                    p2                   pc                  
6                    2                    p3                   pc                  
7                    3                    p4                   pc                  
8                    3                    s4                   s                   
9                    8                    s5                   s                   
10                   3                    p5                   pc                  
11                   8                    p6                   pc                  

绘制成图后
  Oracle高级查询之等级查询 - zhenghaoju700 - zhenghaoju700 的博客

其实就是一种树状结构

针对这种情况 Oracle专门提供了一种特殊查询的方式
(其实我们大家用自连接也可以实现Oracle这种方式)

语法:
SELECT * FROM TESTTREE;
SELECT [LEVEL], column, expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

LEVEL 表示树中Node位于的层
START WITH 表示从那些符合条件的节点开始
CONNECT BY PRIOR 表示连接关系

我们例子的查询方式的SQL:

SELECT LEVEL, ID, FID, NAME, TYPE
FROM TESTTREE  START WITH ID = '1' CONNECT BY PRIOR ID = FID
ORDER BY LEVEL;


LEVEL                  ID                   FID                  NAME                 TYPE                
---------------------- -------------------- -------------------- -------------------- --------------------
1                      1                                         r1                   r                   
2                      2                    1                    s1                   s                   
2                      3                    1                    s2                   s                   
3                      5                    2                    p2                   pc                  
3                      6                    2                    p3                   pc                  
3                      10                   3                    p5                   pc                  
3                      7                    3                    p4                   pc                  
3                      8                    3                    s4                   s                   
3                      4                    2                    p1                   pc                  
4                      9                    8                    s5                   s                   
4                      11                   8                    p6                   pc


按照这样的结果可以很快按树结构一层一层对树进行遍历,快速的画出我们想要的图

用PowerChart绘制出的图形
  Oracle高级查询之等级查询 - zhenghaoju700 - zhenghaoju700 的博客

这个图type为r,s的点按对角线放置 ,type为s的点围绕在相应父节点的周围



  评论这张
 
阅读(168)| 评论(0)
推荐

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017