oracle 数据库中 connect by 和level 的用法

admin管理员组

文章数量:1794759

oracle 数据库中 connect by 和level 的用法

 这几天看sql看到了有关connect by和level的关键字用法，以前都用mysql也没用过这个关键字，感觉挺有用的，就学了下，整理一下学习过程。

      首先，connect by和level都是为了快速的查询层级关系的关键字，在代理关系中，或者权限关系中，经常会有层层嵌套的场景，比如，同行数据的第一个字段是ID，第二个字段是parentID，parentID表示他的上级ID是谁。

       如一条数据，ID为1，parentID为0，表示没有上级，即顶层，grade 为老板        第二条数据，ID为2，parentID为1,表示上级为1，grade 为总监         第三条数据，ID为3,parentID为2,表示上级为2，grade 为经理         这就是一个三级的层级关系事例。

      在这种情况下，如果想知道某个：比如 一级代理的所有二级代理和三级代理的有关行是谁，就很麻烦了。如果使用mysql数据库，就有两种方法

MYSQL

       第一种是在java代码或者别的service层，或工具层中编写有关层级关系解析的方法代码，然后把解析的数据放在redis或者map中（如果不放在redis 或者map中，数据量大的时候，每次调用关系都要重新算一遍是很大的消耗）。

       第二种是在mysql数据库中编写相对的存储过程，在select 查询的时候调用自己封装好的方法，获取相应的数据库信。

   但是oracle 中，则很贴心的提供了对应的方法来解决上述的问题，这就是关键字：connect by 

        其实简单来说，其基本语法是    

   select …,level from tablename           start with 条件1    connect by   prior 子字段id=父字段id    where 条件3;

       例子：

      SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173       CONNECT BY PRIOR  a.FUNCID = a.PARENTID;

         以上述模板为例，LEVEL是查询的层级,START WITH a.FUNCID=3173,表示从该条件开始寻找层级关系，CONNECT BY PRIOR a.FUNCID=a.PARENTID 代表，优先按照a.FUNCID当做基础，该例中（PARENTID的值，绑定的是FUNCID）,所以查找FUNCID=3173的所有儿子孙子，及其层级（儿子LEVEL是2，孙子LEVEL是3）【即查询所有的儿子孙子等等】

范例一：优先查询3174（父节点）的所有孩子

         如果变成CONNECT BY a.FUNCID= PRIOR a.PARENTID，则代表起始为a.FUNCID=3173,优先按照PARENTID查找，即寻找FUNCID=3173这条数据的所有父节点行。【查询所有的父节点】

范例二：优先查询3174（子节点）所有父节点

          这里有点难以理解，但是其实就是如果，PRIOR 放在父字段端，就是寻找STRAT WITH 标注节点的所有的子节点，如果PRIOR 放在子字段端，就是寻找STRAT WITH 标注节点的所有的父节点               因为一个儿子只有一个父亲，而一个父亲可能有多个儿子，所以当PRIOR放在父字段端的时候，可能有多个层级族，因为在找儿子。当放在子字段端的时候，最多只会有一个层级族，因为，他在找爸爸。

       关于 connect by 和level 还有startwith 的详细介绍和延伸，我在学习过程中，找到了两个很好的博客，在此就不转载了，直接转网址了，以供以后自己学习和看此篇博客的人学习。

       oracle connect by用法篇：blog.csdn/wang_yunj/article/details/51040029/

       Oracle中start with...connect by子句的用法：www.blogjava/xzclog/archive/2010/03/05/314642.html

        看完这两篇博客，应该是能懂很多东西的，我这里在尝试和学习的过程中，也做了一些事例，如果在有oracle数据库，且想很快形象的学习有关这块知识，也可以直接在oracle中执行我的事例代码，配上注释，应该能简单不少。

         后续如果有更多的相关知识，会在加在下面的

          事例代码          

--关于LEVEL，START WITH CONNECT BY 的例子

--创建数据库实例 CREATE TABLE "CHANNEL"."AUTH_FUNC" ( "FUNCID" VARCHAR2(255) NOT NULL , "PARENTID" VARCHAR2(255) NOT NULL , PRIMARY KEY ("FUNCID") ) NOCOMPRESS ;

 

INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3173', '0'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3174', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3175', '3174'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3176', '3174'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3177', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3178', '3177'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3179', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3180', '3179'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3181', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3182', '3181'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3183', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3184', '3183'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3185', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3186', '3173'); INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3187', '3173');   select * from AUTH_FUNC order by parentid;

 --输出从1到100

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <100;

 --输出所有数据,这个时候,因为没有在条件中设置层级关系,所以LEVEL为多大都不影响结果

SELECT a.FUNCID,a.PARENTID,LEVEL  FROM AUTH_FUNC a CONNECT BY LEVEL>999;

--输出所有数据,这个时候,因为没有在条件中设置层级关系,所以LEVEL为多大都不影响结果 --但是此条中将LEVEL加入了WHERE条件,所以查询结果受影响

SELECT a.FUNCID,a.PARENTID,LEVEL  FROM AUTH_FUNC a WHERE LEVEL < 1 CONNECT BY LEVEL>999;

 --以上语句只是为了方便理解LEVEL,他其实就是一个可以当做条件的， --限制或表现层数的方法字段     --接下来是CONNECT BY的用法 --想找到父节点是0开始的所有相关子节点及其层数 --也就是FUNCID 为3173的所有子节点(从父节点从父级，从上往下查找，可能获得多个子集分支)

SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173 CONNECT BY PRIOR  a.FUNCID = a.PARENTID order by PARENTID;

  --将PRIOR 放在等于号后面，将会获得不同的结果（从子节点开始查询，获取上层节点） --因为该处FUNCID 就为顶级，作为子节点查询的话，肯定只有一条

SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173 CONNECT BY   a.FUNCID = PRIOR  a.PARENTID;

--原因就是,PRIOR是优先级的意思，放在前面或者后面，或者调换等于号两遍的条件顺序都会获得相反的结果     --如这个例子，就表示了以FUNCID 3175开始,从子节点遍历获取上层节点的例子 --因为是从子节点开始所以,节点族肯定只有一条,即：一个孙子只有一个爷爷

SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3175 CONNECT BY    a.FUNCID = PRIOR  a.PARENTID;

  --如这个例子，就表示了以FUNCID 3174开始,从父节点遍历获取下层子节点的例子 --因为是从父节点开始所以,节点族可能有多条,即：一个爷爷可能有多个儿子和孙子

SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3174 CONNECT BY    PRIOR  a.FUNCID =   a.PARENTID;

  --省略了START WITH 则表示全部满足查询条件的行，作为根节点

SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a CONNECT BY PRIOR a.FUNCID = a.PARENTID order by parentid;

 

本文标签： 数据库中oraclelevelconnect