oracleconnectby和分析报告函数总结材料.docVIP

实用标准文档 文案大全 TOC \o 1-3 \h \z \u 13165 1. connect by 用法总结 2 15282 一、树查询(递归查询) 2 8826 二、列转行sys_connect_by_path() 4 14282 2.分析函数总结 6 397 1.分析函数(OVER) 7 4895 2.分析函数2(Rank, Dense_rank, row_number) 9 11789 3.分析函数3(Top/Bottom N、First/Last、NTile) 9 22982 4.窗口函数 11 24255 5.报表函数 14 connect by 用法总结 一、树查询(递归查询) 作用 对于oracle进行简单树查询(递归查询) 列转行 2.基本语法 select ... from TableName? where Conditional-1 Conditional-1：过滤条件，用于对返回的所有记录进行过滤。 start with Conditional-2 Conditional-2：查询结果重起始根结点的限定条件。 connect by Conditional-3; Conditional-3：连接条件 1)例子： select num1,num2? from table start with num2 = 1008? connect by ?num2 = prior num1?; 2)解释: start with：用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。 prior: 位置很重要（自我总结，和父在一起 则自底向上，即查父 和子在一起 则自顶向下 查子） 例子 原始数据 num1 为父 num2 为子 看下面的图 1. CONNECT_BY_ROOT 返回当前节点的最顶端节点。2. CONNECT_BY_ISLEAF 判断是否为叶子节点，是1，不是0。 3. LEVEL 伪列表示节点深度。4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔。 二、列转行 sys_connect_by_path() 这个函数使用之前必须先建立一个树，否则无用 sys_connect_by_path(字段名, 2个字段之间的连接符号) with?tmp_a as (select 1 a,0 p from dualunion all?select 2,1 from dualunion all?select 3,1 from dualunion all?select 4,3 from dualunion all?select 5,2 from dualunion all?select 6,5 from dual)-- 子全部显示 根--子?? level代表级别select a,p,sys_connect_by_path(a,--),level from tmp_astart with a = 1connect by p = prior a-- 2和2的所有下级去掉 根--子 (开始就要去掉)select a,p,sys_connect_by_path(a,--) from tmp_astart with p = 1 and a 2connect by p = prior a -- 2的所有下级都去掉 根--子 (connect 时去掉)select a,p,sys_connect_by_path(a,--) from tmp_astart with a = 1connect by p = prior a and p 2 --去掉2的分枝 -- 2的下一级去掉 根--子 (where 中去掉)select a,p,sys_connect_by_path(a,--) from tmp_awhere p 2 start with a = 1connect by p = prior a --显示最长的 根--子with?tmp_tab as (select 中国 s,null b from dual?union all?select 广东 s,中国 b from dual?union all?select 湖南 s,中国 b from dual?union all?select 衡阳 s,湖南 b from dual?union all?select 广州 s,广东 b from dual?union all?select 衡东 s,衡阳 b from dual?