精华内容
下载资源
问答
  • oracle中start with和connect by用法理解

    万次阅读 2017-05-22 16:14:06
    Oracle中start with和connect by 用法理解 转自:http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html,多谢博主分享 connect by 是结构化查询中用到的,其基本语法是: 1 select … from ...
    Oracle中start with和connect by 用法理解
    转自:http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html,多谢博主分享

    connect by 是结构化查询中用到的,其基本语法是:
    1 selectfrom tablename
    2 start with 条件1
    3 connect by 条件2
    4 where 条件3;
    例:
    1 select * from table
    2 start with org_id = ‘HBHqfWGWPy’
    3 connect by prior org_id = parent_id;
         简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:org_id,parent_id,那么通过表示每一条记录的parent是谁,就可以形成一个树状结构,用上述语法的查询可以取得这棵树的所有记录,其中:   
            条件1  是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
            条件2 是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR org_id = parent_id;就是说上一条记录的org_id 是本条记录的parent_id,即本记录的父亲是上一条记录。
             条件3 是过滤条件,用于对返回的所有记录进行过滤。
        简单介绍如下:
            在扫描树结构表时,需要依此访问树结构的每个节点,一个节点只能访问一次,其访问的步骤如下:
            第一步:从根节点开始;
            第二步:访问该节点;
            第三步:判断该节点有无未被访问的子节点,若有,则转向它最左侧的未被访问的子节,并执行第二步,否则执行第四步;
            第四步:若该节点为根节点,则访问完毕,否则执行第五步;
            第五步:返回到该节点的父节点,并执行第三步骤。
            总之:扫描整个树结构的过程也即是中序遍历树的过程。

     1.树结构的描述
         树结构的数据存放在表中,数据之间的层次关系即父子关系,通过表中的列与列间的关系来描述,如EMP表中的EMPNO和MGR,EMPNO表示该雇员的编号,MGR表示领导该雇员的人的编号,即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR(除根节点外),通过每个节点的父节点,就可以确定整个树结构。
         在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树型结构关系,其命令格式如下:
    SELECT . . .
    CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
    [START WITH];

         其中:CONNECT BY子句说明每行数据将是按层次顺序检索,并规定将表中的数据连入树型结构的关系中。PRIOR运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,PRIOR运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是自底向上
         在连接关系中,除了可以使用列名外,还允许使用列表达式。START WITH 子句为可选项,用来标识哪个节点作为查找树型结构的根节点,若该子句被省略,则表示所有满足查询条件的行作为根节点。
         START WITH:不但可以指定一个根节点,还可以指定多个根节点。
    2.关于PRIOR
         运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序。

         PRIOR被置于CONNECT BY子句中等号的前面时,则强制从根节点到叶节点的顺序检索,即由父节点向子节点方向通过树结构,我们称之为自顶向下的方式。如:
         CONNECT BY PRIOR EMPNO=MGR
         PIROR运算符被置于CONNECT BY 子句中等号的后面时,则强制从叶节点到根节点的顺序检索,即由子节点向父节点方向通过树结构,我们称之为自底向上的方式。例如:
         CONNECT BY EMPNO=PRIOR MGR
         在这种方式中也应指定一个开始的节点。
    3.定义查找起始节点
         在自顶向下查询树结构时,不但可以从根节点开始,还可以定义任何节点为起始节点,以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
    4.使用LEVEL
         在具有树结构的表中,每一行数据都是树结构中的一个节点,由于节点所处的层次位置不同,所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始,该起始根节点的层号始终为1,根节点的子节点为2, 依此类推。图1.2就表示了树结构的层次。
    5.节点和分支的裁剪
         在对树结构进行查询时,可以去掉表中的某些行,也可以剪掉树中的一个分支,使用WHERE子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。
    6.排序显示
         像在其它查询中一样,在树结构查询中也可以使用ORDER BY 子句,改变查询结果的显示顺序,而不必按照遍历树结构的顺序。
    7. 实例
         oracle 提供了start with connect by 语法结构可以实现递归查询。
    1. 一个简单举例:
    复制代码
     1 SQL> select *  from test;
     2 BILL_MONTH           DAY_NUMBER MSISDN
     3 -------------------- ---------- --------------------
     4 200803                        1 13800
     5 200803                        3 13800
     6 200803                        2 13800
     7 200803                        2 13801
     8 200803                        4 13804
     9 200803                        5 13804
    10 200803                        7 13804
    11 200803                        8 13804
    12 200803                        6 13802
    13 200803                        6 13801
    14 200803                        7 13801
    15 200803                        8 13801
    16 12 rows selected
    17 
    18 SQL>
    19 SQL> select * from test
    20   2       start with day_number=1
    21   3       connect by  prior day_number=day_number-1 and prior msisdn= msisdn
    22   4      ;
    23 
    24 BILL_MONTH           DAY_NUMBER MSISDN
    25 -------------------- ---------- --------------------
    26 200803                        1 13800
    27 200803                        2 13800
    28 200803                        3 13800
    29 
    30 SQL>
    复制代码
        上面的语句查找出了从1开始,并且day_number 逐渐+1 递增的,并且 msisdn 相同的哪些个数据.
    2. start with  connect by 语法结构
        如上面说看到的例子,其语法结构为start with condition  connect by  condition (含 prior 关键字)
    start with conditon 给出的seed 数据的范围, connect by  后面给出了递归查询的条件,prior 关键字表示父数据,prior 条件表示子数据需要满足父数据的什么条件。在下面的这个start with connect by 结构中,就表示查找出了从1开始,父数据的day_number等于子数据的day_number-1而且父数据的msisdn=子数据的msisdn.
    start with day_number=1
    connect by  prior day_number=day_number-1 and prior msisdn= msisdn

    展开全文
  • scala中sorted,sortWith,sortBy用法详解

    万次阅读 2017-07-23 23:07:51
    scala的集合中提供了三种排序的方式:sorted,sortWith,sortBy。那么这三种方式有什么不同呢?下面我们结合源码来分析一下1.sorted先来看看scala中sorted的源码。 def sorted[B >: A](implicit ord: Ordering[B]): ...

    项目github地址:bitcarmanlee easy-algorithm-interview-and-practice
    欢迎大家star,留言,一起学习进步

    scala的集合中提供了三种排序的方式:sorted,sortWith,sortBy。那么这三种方式有什么不同呢?下面我们结合源码来分析一下

    1.sorted

    先来看看scala中sorted的源码。

      def sorted[B >: A](implicit ord: Ordering[B]): Repr = {
        val len = this.length
        val arr = new ArraySeq[A](len)
        var i = 0
        for (x <- this.seq) {
          arr(i) = x
          i += 1
        }
        java.util.Arrays.sort(arr.array, ord.asInstanceOf[Ordering[Object]])
        val b = newBuilder
        b.sizeHint(len)
        for (x <- arr) b += x
        b.result
      }
    

    源码中有两点值得注意的地方:
    1.sorted方法中有个隐式参数ord: Ordering。
    2.sorted方法真正排序的逻辑是调用的java.util.Arrays.sort。

    2.sortBy

    看看sortBy的源码,很简单。

      def sortBy[B](f: A => B)(implicit ord: Ordering[B]): Repr = sorted(ord on f)
    

    sortBy最后也是调用的sorted方法。不一样的地方在于,sortBy前面还需要提供一个属性。

    3.sortWith

    sortWith的源码如下。

    def sortWith(lt: (A, A) => Boolean): Repr = sorted(Ordering fromLessThan lt)
    

    跟前面两个不同的是,sortWith需要传入一个比较函数用来比较!

    4.实例

    理论部分说完了,下面来干货

    object ImplicitValue {
    	
    	implicit val KeyOrdering = new Ordering[String] {
    		override def compare(x: String, y: String) : Int = {
    			y.compareTo(x)
    		}
    	}
    }
    
    

    首先定义了一个隐式比较器。

    	def test1() = {
    		import ImplicitValue.KeyOrdering
    		val list = List( "a", "g", "F", "B", "c")
    		val sortedList = list.sorted
    		println(sortedList) // List(g, c, a, F, B)
    	}
    

    注意因为我们将隐式比较器import了进来,这个时候sorted排序的规则是按照我们自定义的比较器进行比较。在我们自定义的比较器中,定义的是按字符串逆序,所以最终的输出结果为字符串按从大到小的顺序排列!

    再来看看sortWith的用法。

    	//忽略大小写排序
    	def compareIngoreUpperCase(e1: String, e2: String) : Boolean = {
    		e1.toLowerCase < e2.toLowerCase
    	}
    	
    	def test2() = {
    		val list = List( "a", "g", "F", "B", "c")
    		val sortWithList1 = list.sortWith(_ < _) // List(B, F, a, c, g)
    		val sortwithList2 = list.sortWith((left, right) => left < right) //List(B, F, a, c, g)
    		val sortwithList3 = list.sortWith(compareIngoreUpperCase) // List(a, B, c, F, g)
    		println(sortWithList1)
    		println(sortwithList2)
    		println(sortwithList3)
    	}
    

    本例中, sortWithList1与sortWithList2最终的结果是一致的,只不过写法不一样而已,都是按字符串从小到大的顺序排列。sortwithList3则是按照传入的compareIngoreUpperCase函数进行排序!

    最后看看sortBy的代码

    	def test3() = {
    		val m = Map(
    			-2 -> 5,
    			2 -> 6,
    			5 -> 9,
    			1 -> 2,
    			0 -> -16,
    			-1 -> -4
    		)
    		//按key排序
    		m.toList.sorted.foreach{
    			case (key, value) =>
    				println(key + ":" + value)
    		}
    		println
    		
    		//按value排序
    		m.toList.sortBy(_._2).foreach {
    			case (key, value) =>
    				println(key + ":" + value)
    		}
    	}
    

    最后的输出结果为:

    -2:5
    -1:-4
    0:-16
    1:2
    2:6
    5:9
    
    0:-16
    -1:-4
    1:2
    -2:5
    2:6
    5:9
    
    
    展开全文
  • oracle connect by用法以及with递归

    千次阅读 2018-09-23 15:02:45
    在oracle中start with connect by (prior) 用来对树形结构的数据进行查询。其中start with conditon 给出的是数据搜索范围, connect by后面给出了递归查询的条件; 涉及的伪列及函数: – connect_by_isleaf 伪列 – ...

    环境:11g
    准备:
    在oracle中start with connect by (prior) 用来对树形结构的数据进行查询。其中start with conditon 给出的是数据搜索范围, connect by后面给出了递归查询的条件;
    涉及的伪列及函数:
    – connect_by_isleaf 伪列
    – connect_by_root()获取根节点
    – sys_connect_by_path(a,b) 显示分层路径
    – connect_by_iscycle 是否循环

    语法:
    [start with]
    connect by [nocycle] prior 条件
    [and]

    -- 表结构
    drop table   menu;
    create table menu(
     mid varchar2(64) not null,
     parent_id varchar2(64) not null,
     mname varchar2(100) not null,
     mdepth number(2) not null,
     primary key (mid)
    );
    
    -- 初始化数据
    -- 顶级菜单
    insert into menu values ('100000', '0', '顶级菜单1', 1);
    insert into menu values ('200000', '0', '顶级菜单2', 1);
    insert into menu values ('300000', '0', '顶级菜单3', 1); 
    
    -- 父级菜单
    -- 顶级菜单1 直接子菜单
    insert into menu values ('110000', '100000', '菜单11', 2);
    insert into menu values ('120000', '100000', '菜单12', 2);
    insert into menu values ('130000', '100000', '菜单13', 2);
    insert into menu values ('140000', '100000', '菜单14', 2); 
    -- 顶级菜单2 直接子菜单
    insert into menu values ('210000', '200000', '菜单21', 2);
    insert into menu values ('220000', '200000', '菜单22', 2);
    insert into menu values ('230000', '200000', '菜单23', 2); 
    -- 顶级菜单3 直接子菜单
    insert into menu values ('310000', '300000', '菜单31', 2); 
    
    -- 菜单13 直接子菜单
    insert into menu values ('131000', '130000', '菜单131', 3);
    insert into menu values ('132000', '130000', '菜单132', 3);
    insert into menu values ('133000', '130000', '菜单133', 3);
    
    -- 菜单132 直接子菜单
    insert into menu values ('132100', '132000', '菜单1321', 4);
    insert into menu values ('132200', '132000', '菜单1332', 4);
    
    

    测试:

    
    --看prior修饰的对象,如果是父id,则获取的是父节点数据,反之获取的是子节点数据
    --1找指定节点的所有父节点(直接或间接) 
    select *
      from menu aa
     start with aa.mid = '130000'
    connect by aa.mid = prior aa.parent_id;
    --或者
    select *
      from menu aa
     start with aa.mid = '130000'
    connect by  prior aa.parent_id = aa.mid;
    
    
    --如果不用树查询,如何实现呢?
    --需要知道查询的次数,然后不断的union all,比较麻烦
    --或者更改表设计
    select aa.* from menu aa where aa.mid='130000'
    union all
    select b.* from menu b where b.mid=(select aa.parent_id from menu aa where aa.mid='130000');
    
    
    
    
    --2找到指定节点的所有子节点(直接或间接)  
    select *
      from menu aa
     start with aa.mid = '130000'
    connect by prior aa.mid = aa.parent_id;
    
    --或者
    select aa.*, level 层级号
      from menu aa
     start with aa.mid = '130000'
    connect by aa.parent_id = prior aa.mid;
    
    
    
    --3.只获取指定节点的直接父节点
    select * from menu aa 
    where  aa.mid='130000';
    
    
    --4.只获取指定节点的直接字节点,利用level限制
    select *
      from (select aa.*, level lev
              from menu aa
             start with aa.mid = '130000'
            connect by aa.parent_id = prior aa.mid) tt
     where tt.lev = 2; --注意:在外面限制,里面限制没用
    --或者
    select * from menu aa where aa.parent_id='130000';
    
    
    --4.1 获取不是'130000'分支的树
    select aa.*, level 层级号
      from menu aa
    connect by aa.parent_id = prior aa.mid
           and aa.mid != '130000'
           and aa.parent_id;
    
    
    --5.获取指定菜单的子菜单的个数,包括自已
    select count(1) from 
    (
    select * from menu aa 
    start with aa.mid='130000' 
    connect by prior  aa.mid = aa.parent_id
    );
    
    --5.1 获取每一个菜单的子菜单的个数(包括自已,包括直接或间接)
    --思路: 先求出每个菜单的父菜单,再根据父菜单分组,即可得到每个菜单的子菜单个数
    select aa.mid,max(aa.mname),count(1) 子菜单个数 from menu aa 
    group by aa.mid
    connect by aa.mid=prior aa.parent_id
    order by aa.mid;
    
    --如果要不包括自已
    select aa.mid,max(aa.mname),count(1)-1 子菜单个数 from menu aa 
    group by aa.mid
    connect by aa.mid=prior aa.parent_id
    order by aa.mid;
    
    
    
    
    --6 形象展示菜单结构
    --展示 130000的结构
    -- connect_by_isleaf 伪列  
    -- connect_by_root()获取根节点
    -- sys_connect_by_path(a,b) 显示分层路径
    -- siblings by,能保持兄弟关系(层次),优先层次显示,同一层次的再按照字典顺序排序。
    select aa.mid,
           lpad('|-', level * 2, ' ') || aa.mname,
           aa.parent_id,
           decode(connect_by_isleaf, 0, '根节点', 1, '  叶子节点') isleaf,
           connect_by_root(aa.mname) 根节点,
           sys_connect_by_path(aa.mname,'=>') 分层路径 
      from menu aa
    start with aa.mid = '130000'
    connect by aa.parent_id = prior aa.mid
    order siblings by aa.mname;
    
    
    --6.1展示 所有顶级节点的结构
    select * from (
    select aa.mid,
           lpad('|-', level * 2, ' ') || aa.mname 菜单结构,
           aa.parent_id,
           decode(connect_by_isleaf, 0, '根节点', 1, '  叶子节点') isleaf,
           connect_by_root(aa.mname) 根节点,
           connect_by_root(aa.mid) rootid
      from menu aa
      connect by aa.parent_id = prior aa.mid) tt
      where tt.rootid in(select a.mid from menu a where a.parent_id='0') 
      order by tt.rootid,tt.mid;
    
    

    顶级节点

    
    --6.2 如果要去掉某个分支呢? 先把全部的树查询出来,再minus掉指定分支;
    
    --7. 获取指定范围的树形数据
    --比如获取20部门的 顶级节点的子节点,已知 mgr=null在部门10
    select * from emp e
    where e.deptno=20
    start with e.mgr is null
    connect by prior e.empno=e.mgr;
     --发现竟然有数据,说明这个查询肯定是不对的,因为此时的where作用的是整个树形的查询结果,而不是emp
    --所有在查询的一开始就要限制范围
    --正确写法
    select *
      from (select * from emp e where e.deptno = 20) e
     start with e.mgr is null
    connect by prior e.empno = e.mgr;
    
    8.where过滤的问题,where的过滤条件针对的是树查询结果进行过滤的;
    注意:这说的是where的过滤条件(带常量的),不是连接条件!!!
    select *
      from menu aa 
      where aa.mid!='130000'
     start with aa.mid = '130000'
    connect by prior aa.mid = aa.parent_id;
    
    

    在这里插入图片描述在这里插入图片描述

    这个时候有5条记录;相当于把’130000’的直接父节点的记录删除掉了;
    从执行计划可以看到,是在最后把结果求出来后,再过滤的;

    --如果把条件放在connect by后面呢?
    select *
      from menu aa 
     start with aa.mid = '130000'
    connect by prior aa.mid = aa.parent_id
    and   aa.mid!='130000'; 
    
    

    在这里插入图片描述在这里插入图片描述

    发现结果没有变化,为啥呢?
    猜测是在递归循环的过程中,无法过滤start with的mid节点条件;
    导致过滤无效;下面的列子会说明的;

    再看:
    select *
    from menu aa
    start with aa.mid = ‘130000’
    connect by prior aa.mid = aa.parent_id
    and aa.mid!=‘132000’;
    在这里插入图片描述
    这个是在递归循环过程中的过滤条件,在把mid!='132000’时,源头132000没了.那么涉及的parent_id自然没有132000;
    等价于把的mid为132000以及parent_id为132000的全部去掉;
    这种在循环的过程中进行过滤的,过滤效果相对于where过滤条件,过滤的更多;

    select * from emp e
    where e.deptno=20
    start with e.mgr is null
    connect by prior e.empno=e.mgr;
    在这里插入图片描述
    select * from emp e
    start with e.mgr is null
    connect by prior e.empno=e.mgr
    and e.deptno=20;
    在这里插入图片描述
    也可以看到,connect by 后的and条件无法过滤掉start with的开始条件;

    
    --9.cconnect by不要prior呢,可以用来创建数据;
    select * from menu aa 
    start with aa.mid='130000' 
    connect by  aa.mid = aa.parent_id;
    --此时等价于
    select * from menu aa 
    where  aa.mid='130000';
    
    select rownum,level from dual  connect by  rownum<500;
    
    
    
    对于上面的递归查询,在11g之前只能用start with ... connnect by prior来实现,
    从版本11GR2开始,ORACLE支持递归的WITH, 即允许在WITH子查询的定义中对自身引用。
    其他数据库如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持这一特性;
    语法:
    WITH 
    ①  query_name ([c_alias [, c_alias]...])
    ②  AS (subquery)
    ③  [search_clause]
    ④  [cycle_clause]
    ⑤  [,query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]... 
    ①这是子查询的名称,和以往不同的是,必须在括号中把这个子查询的所有列名写出来。
    ②AS后面的subquery就是查询语句,递归部分就写在这里。
    ③遍历顺序子句,可以指定深度优先或广度优先遍历顺序。
    ④循环子句,用于中止遍历中出现的死循环。
    ⑤如果还有其他递归子查询,定义同上。
    SELECT empno,
           ename,
           job,
           mgr,
           deptno,
           level,
           SYS_CONNECT_BY_PATH(ename, '\') AS path,
           CONNECT_BY_ROOT(ename) AS top_manager
      FROM EMP
     START WITH mgr IS NULL 
    CONNECT BY PRIOR empno = mgr;
    等价:
    
    WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来
     SELECT empno, ename, job, mgr, deptno ---- 先写锚点查询,用START WITH的条件
      ,1 AS the_level ---- 递归起点,第一层
      ,'\'||ename ---- 路径的第一截
      ,ename AS top_manager ---- 原来的CONNECT_BY_ROOT
     FROM EMP
     WHERE mgr IS NULL ---- 原来的START WITH条件
     UNION ALL ---- 下面是递归部分
     SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一层数据,来自要遍历的emp表
      ,1 + t.the_level  ---- 递归层次,在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列
      ,t.path||'\'||e.ename ---- 把新的一截路径拼上去
      ,t.top_manager  ---- 直接继承原来的数据,因为每个路径的根节点只有一个
     FROM t, emp e   ---- 典型写法,把子查询本身和要遍历的表作一个连接
     WHERE t.empno = e.mgr  ---- 原来的CONNECT BY条件
    ) ---- WITH定义结束
    SELECT * FROM T
    ;
    with循环参考:https://blog.csdn.net/cacasi2568/article/details/55224422
    
    

    好,用法就介绍到这了,不知道内有没收获呀
    下一篇将介绍connect by的优化,这个是个难点;

    end by ysy

    展开全文
  • oracle 的 start with connect by 用法

    千次阅读 2012-11-27 17:38:00
    转帖地址:... ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询) Syntax 1 CONNECT BY [NOCYCLE] START WITH  Sy

    转帖地址:http://www.cnblogs.com/caroline/archive/2011/12/25/2301083.html


    ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询)

    Syntax 1CONNECT BY [NOCYCLE] <condition> START WITH <condition>
    Syntax 2START WITH <condition> CONNECT BY [NOCYCLE] <condition>

    参考网址:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489772591421

                http://psoug.org/reference/connectby.html

                http://www.oradev.com/connect_by.jsp

               http://philip.greenspun.com/sql/trees.html
    查找员工编号为7369的领导:

    1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO  START WITH E.EMPNO = 7876
    2 ORDER BY LEVEL DESC

    "start with" -- this identifies all LEVEL=1 nodes in the tree

    "connect by" -- describes how to walk from the parent nodes above to their children and
    their childrens children.

    Easiest to use an example on emp. If we start with "where mgr is NULL", we generate the
    set of employees that have no mgr (they are the top of the tree). If we

    CONNECT BY PRIOR EMPNO = /* current */ MGR

    that will take all of the PRIOR records (the start with at first) and find all records
    such that the MGR column equals their EMPNO (find all the records of people managed by
    the people we started with).


    使用WITH语句优化查询结果:优化等级

    复制代码
     1 WITH A AS
     2  (SELECT MAX(LEVEL) + 1 LVL
     3     FROM EMP E
     4   CONNECT BY PRIOR E.MGR = E.EMPNO
     5    START WITH E.EMPNO = 7876
     6    ORDER BY LEVEL DESC)
     7 SELECT A.LVL 最高等级加1,
     8        LEVEL 当前等级,
     9        A.LVL - LEVEL 优化后等级,
    10        E.*  FROM A,
    11        EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 7876 ORDER BY LEVEL DESC
    复制代码

    查找员工编号为7839的所有下属(7839为king):

    1 SELECT LEVEL 等级, E.*
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.EMPNO = 7839

    --构造整个的层次结构

    1 select lpad(' ',level*2,' ')||ename ename, empno, mgr
    2     from emp
    3     START WITH MGR IS NULL
    4     CONNECT BY PRIOR EMPNO = MGR

    So, KING is the start with set then JONES BLAKE and CLARK fall under him. Each of them
    becomes the PRIOR record in turn and their trees are expanded.



    使用Connect By 结合 level构造虚拟行:

    1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

    使用rownum实现类似的功能:

    1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

    ---------------------待续-----------------------

    使用UNION ALL构造两层节点的树:

    视图如下所示:

    复制代码
     1 CREATE OR REPLACE VIEW TREE_VIEW AS
     2 SELECT
     3  '1' AS rootnodeid,
     4  'xxxx有限责任公司' AS treename,
     5  '-1'  AS parent_id
     6 FROM dual
     7 UNION
     8 SELECT
     9   to_char(d.deptno),
    10   d.dname || '_' ||d.loc,
    11   '1' AS parent_id
    12  FROM dept d;
    复制代码

    查询语句:

    1 SELECT T.*, LEVEL
    2   FROM TREE_VIEW T
    3  START WITH T.PARENT_ID = '-1'
    4 CONNECT BY PRIOR T.ROOTNODEID = T.PARENT_ID

    -----以下为更新内容:

    1、先查看总共有几个等级:

    1 SELECT COUNT(LEVEL)
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.MGR IS NULL;

    2、查看每个等级的人数。主要是通过LEVEL进行GROUP BY

    1 SELECT COUNT(LEVEL)
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.MGR IS NULL
    5  GROUP BY LEVEL;

    3、Oracle 10g提供了一个简单的connect_by_isleaf=1,

    0 表示非叶子节点

    1 SELECT LEVEL AS 等级, CONNECT_BY_ISLEAF AS 是否是叶子节点, E.*
    2   FROM EMP E
    3 CONNECT BY PRIOR E.EMPNO = E.MGR
    4  START WITH E.MGR IS NULL

    4、SYS_CONNECT_BY_PATH

    Oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自动转

    换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

    1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>')
    4   FROM EMP E
    5 CONNECT BY PRIOR E.EMPNO = E.MGR
    6  START WITH E.MGR IS NULL;

    5、修剪树枝和节点:

        过滤掉编号是7566的数据(修剪节点),他指的是把这个节点给裁掉,但是并没有破坏树结构,它的子节点还是可以正常的显示。

    复制代码
    1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    4        E.*
    5   FROM EMP E
    6 WHERE e.empno != 7566
    7 CONNECT BY PRIOR E.EMPNO = E.MGR
    8  START WITH E.MGR IS NULL;
    复制代码

    裁掉编号是7698的节点和它的子节点:

    复制代码
    1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    4        E.*
    5   FROM EMP E
    6 CONNECT BY PRIOR E.EMPNO = E.MGR
    7        AND E.EMPNO != 7698
    8  START WITH E.MGR IS NULL;
    复制代码

    6、CONNECT_BY_ROOT的使用,oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

    复制代码
    1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        CONNECT_BY_ROOT ENAME,
    4        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    5        E.*
    6   FROM EMP E
    7 CONNECT BY PRIOR E.EMPNO = E.MGR
    8  START WITH E.MGR IS NULL;
    复制代码

     

    对于层次查询如果用order by排序,比如order by last_name则是先做完层次获得level,然后按last_name 排序,这样破坏了层次,比如特别关注某行的深度,按level 排序,也是会破坏层次的。在oracle10g中,增加了siblings 关键字的排序。

    语法:order siblings by <expre>

    它会保护层次,并且在每个等级中按expre排序。

    复制代码
    1 SELECT LEVEL AS 等级,
    2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
    3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
    4        E.*
    5   FROM EMP E
    6 CONNECT BY PRIOR E.EMPNO = E.MGR 
    7  START WITH E.MGR IS NULL
    8  ORDER SIBLINGS BY  E.ENAME;
    复制代码

    connect_by_iscycle(存在循环,将返回1,否则返回0)

    The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
    You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

    没有人可以和生活讨价还价,所以只要活着,就一定要努力。


    展开全文
  • hive group by with cubegrouping sets用法

    千次阅读 2019-09-12 15:43:52
    1.group by with cube 此方式对每一个统计维度打cube,维度的空值自动会变成null,使用coalesce(x x x x,‘all’)把对应维度的null转为all,就是该字段的合计值 select coalesce(page_city_name,'all') as page_...
  • Oracle中start with connect by prior用法

    万次阅读 2019-05-09 08:59:57
    最近看到Oracle中有一个很好用的查询,它就是start with connect by prior,说白了就是递归算法。 如果表中存在层次数据,则可以使用层次化查询子句查询出表中行记录之间的层次关系 基本语法: [ START WITH ...
  • 相信很多朋友都经常会使用Group By来进行数据的统计,本文介绍可能大家比较少用,但实际上有时候又很有用的三个Group By查询。 1. group by all ...”也就是,group by all必须where子句一起使用。
  • Oracle start with connect by prior 用法

    千次阅读 2014-01-20 12:48:37
    语法:   select *  from  表名   where  条件 1  start with  条件 2  connect by prior  当前表字段 = 级联表字段   start with 与 connect by prior 语句完成递归记录, ...start with
  • reduceByKeygroupByKey区别用法

    万次阅读 多人点赞 2018-05-19 13:40:52
     //groupByKey reduceByKey 的区别:   //他们都是要经过shuffle的,groupByKey在方法shuffle之间不会合并原样进行shuffle,   //reduceByKey进行shuffle之前会先做合并,这样就减少了shuffle的io传送,所以...
  • 这个是用在聚合函数的用法。当我们在用聚合函数的时候,一般都要用到GROUP BY 先进行分组,然后再进行聚合函数的运算。运算完后就要用到HAVING 的用法了,就是进行判断了,例如说判断聚合函数的值是否大于某一个值...
  • with rollup的用法

    千次阅读 2019-12-24 18:14:55
    Mysql中有一个with rollup是用来在分组统计数据的基础上再进行统计汇总,即用来得到group by的汇总信息; 表中数据有: 没有with rollup的查询 select count(*),age from age group by age; 结果: 有with rollup...
  • Oracle with语句的用法

    千次阅读 2019-07-19 09:28:46
    Oracle with语句的用法 Oracle数据库中,使用with语句可以实现子查询,提高语句执行的效率,下文对with语句的用法作了详细的介绍,供您参考学习。 Oracle with语句是经常可以见到的语句,下面就为您详细介绍...
  • python的with用法

    万次阅读 多人点赞 2016-10-06 21:18:11
    1.With语句是什么?有一些任务,可能事先需要设置,事后做清理工作。对于这种场景,Python的with语句提供了一种非常方便的处理方式。一个很好的例子是文件处理,你需要获取一个文件句柄,从文件中读取数据,然后关闭...
  • Oracle中的as和with as 的用法

    千次阅读 2019-04-23 14:59:03
    关于Oracle中的as和with as 的用法 在Oracle中as关键字不能用于指定表的别名,在Oracle中指定表的别名时只需在原有表名表的别名之间用空格分隔即可, 但可以用于指定列的别名,但在存储过程中如果列的别名与原有...
  • sql语句中with as 的用法

    万次阅读 多人点赞 2019-03-06 20:23:21
    with tmp as (select * from tb_name) –针对多个别名 with tmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * from tb_name3), … –相当于建了个e临时表 with e as (select *...
  • oracle with as用法

    千次阅读 2019-10-12 16:00:09
    withas语法 相当于建了个临时表 ,或者sql的别命名 with tmp1 as(select*from aa where id="001"), tmp2as(select*frombb where id="001"), tmp3as(select*from cc where id="001"), ...select *from tmp1 , tmp2,...
  • Oracle之with asupdate用法

    千次阅读 2018-11-29 12:05:00
    update test b set b.code=(with t as(select t.id,code||'_'||row_number() over(partition by code order by id) codefrom test t)select a.code from t a where a.ID=b.ID); 很多金融统计中,语句相当复杂。有...
  • connect by prior start with 用法

    千次阅读 2008-01-21 11:06:00
    Oracle用Start with...Connect By子句递归查询 Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。创建示例表:CREATE TABLE TBL_TEST( ID NUMBER, NAME VARCHAR2(100 BYTE), PID ...
  • SQL With As 用法

    万次阅读 2018-10-07 16:17:15
    SQL With As 用法 一.WITH AS的含义 WITH AS短语,也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,...
  • @Resource与@Autowired用法区别

    万次阅读 多人点赞 2018-09-11 09:32:14
    spring中,@Resource@... 下面,根据自己的学习,整理下这两个注解使用中的共同点不同点,及用法上的不同。  共同点  @Resource@Autowired都可以作为注入属性的修饰,在接口仅有单一实现类时,两...
  • start with connect by prior 递归查询用法

    千次阅读 2018-03-26 16:24:11
    下面看下start with子句中选择不同的列的区别: 以查询叶子结点(往下遍历)为例 结果很明显,原意是要以7为父结点,遍历其子结点,左图取的是父结点列的值,结果符合原意;右图取的是子结点列的值,结果多余的显示...
  • with ties 的用法

    千次阅读 2015-11-12 18:34:12
    create table #Stuscore ( name nvarchar(20), score int ) insert into #Stuscore values ('陈一',100),('张二',100),('王五',90),('李四',...select top 1 with ties * from #Stuscore order by score desc
  • 关于Oracle中的as和with as 的用法

    万次阅读 2016-03-22 09:28:37
    在Oracle中as关键字不能用于指定表的别名,在Oracle中指定表的别名时只需在原有表名表的别名之间用空格分隔...在查询oracle中as关键字的用法的时候,发现oracle中with as的用法,觉得非常有用,转载一篇大神的博客
  • 相信很多朋友都经常会使用Group By来进行数据的统计,本文介绍可能大家比较少用,但实际上有时候又很有用的三个Group By查询。 1. group by all ...”也就是,group by all必须where子句一起使用。 我们使用S

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 128,534
精华内容 51,413
关键字:

with和by的用法区别