表数据的树结构

今天发现公司的一个table的数据是个树结构，公司-子公司-子子公司-子子子公司...如果我要按树返回结果咋办？

搜到如下SQL，并整理如下知识点：

 

SELECT     nd, SUM (js) sum_js,
           MAX (LTRIM (SYS_CONNECT_BY_PATH (jm, ','), ',')) sum_jm
      FROM (SELECT nd, js, jm,
                   ROW_NUMBER () OVER (PARTITION BY nd ORDER BY js) rn
              FROM tmp10)
START WITH rn = 1
CONNECT BY PRIOR rn + 1 = rn AND PRIOR nd = nd
  GROUP BY nd;

 

1） over partition by与group by 的区别 ：over 是分析函数，group是分组

 

 SELECT deptno, ename, sal,
       SUM (sal) OVER (PARTITION BY deptno ORDER BY ename) 部门连续求和,
                                                      --各部门的薪水"连续"求和
       SUM (sal) OVER (PARTITION BY deptno) 部门总和,
                                           -- 部门统计的总和，同一部门总和不变
         100
       * ROUND (sal / SUM (sal) OVER (PARTITION BY deptno), 4) "部门份额(%)",
       SUM (sal) OVER (ORDER BY deptno, ename) 连续求和,
                                                    --所有部门的薪水"连续"求和
                                                        SUM (sal) OVER ()
                                                                         总和,
                    -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
       100 * ROUND (sal / SUM (sal) OVER (), 4) "总份额(%)"
  FROM emp

 

 2） START WITH rn = 1  如果某行的rn=1那么开始

   CONNECT BY PRIOR rn + 1 = rn    上一行数据 rn +加1的值等于这一行的rn值

3）自从Since Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。

有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。

select connect_by_isleaf,sys_connect_by_path(company_code,',')
from fin_company_group_detail
start with parent_company_code='I0003'
connect by parent_company_code=prior company_code

 

4） 为了方便我们建立一个树，有时候我们还会添加rownum

 

select no,sys_connect_by_path(q,',')
from (
select no,q,
       no+row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn

 ow_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

　　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

　　row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

　　rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．

　　lag（arg1,arg2,arg3): 
arg1是从其他行返回的表达式 
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。 
arg3是在arg2表示的数目超出了分组的范围时返回的值。