递归查询并统计的问题，，，请教！先谢了

Question

现在有两个表，表结构如下：
Ａ表：
id ,dept
1    a
2    b
3    c
4    aa
5    bb
6    cc
......
B 表
id  parent_id dept
1    0         a
2    1         b
3    1         c
4     2        bb
5     2        cc
......　　a就是第一个级别，b和c是第二个级别，bb和cc是第三个,bbb和ccc是第四个级别这样一个tree，
两个表通过dept关联
我想部门做一个统计 
第一个级别：部门为a包括所有a的分支的总数 
第二个级别：部门为b/c/.包括所有b/c/.所属的分支的总数 
第三个级别：部门为bb/cc/..包括所有bb/cc/..所属的分支的总数 
第四个级别：部门为bbb/ccc/...包括所有bbb/ccc/...所属的分支的总数  
得到结果类似：
a        10
****************
b        5
　　　　　　　　　　　
c        5
****************
bb       3
cc       2
***********
bbb 
ccc
这样我的sql应该怎么实现？谢谢
        

Answer 1

你这个树型结构做的太滥了,应该有个父ID,这样会做好多
比如
id ,dept  pid
1    a     0
2    b     1
3    c     1
4    aa    1
5    bb    2
6    cc    3

Answer 2

B 表 
id  parent_id dept 
1    0        a 
2    1        b 
3    1        c 
4    2        bb 
5    2        cc 
parent_id 不就是你的pid,呵呵

Answer 3

噢,这样啊,这样可以用start with connect by 来做,我看看

Answer 4

start with connect by 
可以进行从根接点到子接点的遍历
遍历出来的是全部的部门
如果我用 
a.dept=b.dept的话
我不知道怎么能得到各个级别的统计

Answer 5

可以分组统计
用3个union
不过前两天看到的一个取得根目录的函数忘记了,我要去找下,刚刚忙了一阵子,没空看

Answer 6

FYI:http://sating.itpub.net/post/5231/243259

Answer 7

KAO,我一想到楼上的,楼上的就出现,而且我想要什么,他就提供什么

Answer 8

 想了下,觉得先个函数容易实现点CREATE OR REPLACE FUNCTION easytvc.fn_get_child_num (in_id NUMBER)
   RETURN NUMBER
IS
   tot_num   NUMBER (10);
BEGIN
   SELECT COUNT (*)
     INTO tot_num
     FROM (SELECT     b.ID
                 FROM b
           START WITH ID = in_id
           CONNECT BY PRIOR ID = parent_id
             ORDER BY LEVEL);   RETURN tot_num;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN 0;
   WHEN OTHERS
   THEN
      RETURN -1;
END fn_get_child_num;然后
 select b.dept, fn_get_child_num(b.id) num
from b

Answer 9

函数里的ORDER BY不需要
CREATE OR REPLACE FUNCTION easytvc.fn_get_child_num (in_id NUMBER)
   RETURN NUMBER
IS
   tot_num   NUMBER (10);
BEGIN
   SELECT COUNT (*)
     INTO tot_num
     FROM (SELECT     b.ID
                 FROM b
           START WITH ID = in_id
           CONNECT BY PRIOR ID = parent_id
             );   RETURN tot_num;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN 0;
   WHEN OTHERS
   THEN
      RETURN -1;
END fn_get_child_num;

Answer 10

不知道结果对不对，而且下面的语句必须要在Oracle版本是10g的环境中才行，试试吧～～
SQL> select * from cp_test;       CID  PARENT_ID DEPT
---------- ---------- ----------
         1          0 a
         2          1 b
         3          1 c
         4          2 bb
         5          2 cc
         6          5 bbb
         7          6 z
         8          1 xf8 rows selectedSQL> 
SQL> select cid, max(counts) "Total_counts", min(levs) - 1 "level_number"
  2    from (select cid,
  3                 root_child,
  4                 count(1) over(partition by cid) as counts,
  5                 max(lev) over(partition by root_child) as levs
  6            from (select cp.cid,
  7                         level lev,
  8                         connect_by_root(cid) root_child
  9                    from cp_test cp
 10                   start with parent_id <> 0
 11                  connect by prior parent_id = cid) c1
 12           where lev <> 1) p1
 13   group by cid
 14   order by 3;       CID Total_counts level_number
---------- ------------ ------------
         1            7            1
         2            4            2
         5            2            3
         6            1            4SQL> 

Answer 11

如果是要显示dept的话:
SQL> select dept, max(counts) "Total_counts", min(levs) - 1 "level_number"
  2    from (select dept,
  3                 root_child,
  4                 count(1) over(partition by dept) as counts,
  5                 max(lev) over(partition by root_child) as levs
  6            from (select cp.dept,
  7                         level lev,
  8                         connect_by_root(cid) root_child
  9                    from cp_test cp
 10                   start with parent_id <> 0
 11                  connect by prior parent_id = cid) c1
 12           where lev <> 1) p1
 13   group by dept
 14   order by 3;DEPT       Total_counts level_number
---------- ------------ ------------
a                     7            1
b                     4            2
cc                    2            3
bbb                   1            4SQL> 

Answer 12

WITH ss AS (SELECT LEVEL S, n.*
          FROM (SELECT B.ID BID, B.PARENT_ID PID, B.DEPT, A.ID AID
                  FROM A, B
                 WHERE A.DEPT = B.DEPT) n
         START WITH PID = 0
        CONNECT BY PRIOR AID = PID),
 bb AS (SELECT S,(SUM(COUNT(1)) OVER(PARTITION BY s)) lc,
       (SUM(COUNT(1)) OVER()) - SUM(COUNT(1)) OVER(ORDER BY S RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cc
  FROM ss
 GROUP BY S)
 SELECT ss.dept,bb.cc include_count,bb.lc level_count,bb.s s_level FROM bb,ss WHERE ss.s=bb.s ORDER BY bb.s
; 

Answer 13

WITH ss AS (SELECT LEVEL S, n.*
          FROM (SELECT B.ID BID, B.PARENT_ID PID, B.DEPT, A.ID AID
                  FROM A, B
                 WHERE A.DEPT = B.DEPT) n
         START WITH PID = 0
        CONNECT BY PRIOR AID = PID),
 bb AS (SELECT S,(SUM(COUNT(1)) OVER(PARTITION BY s)) lc,
       (SUM(COUNT(1)) OVER()) - SUM(COUNT(1)) OVER(ORDER BY S RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cc
  FROM ss
 GROUP BY S)
 SELECT ss.dept,bb.cc include_count,bb.lc level_count,bb.s s_level FROM bb,ss WHERE ss.s=bb.s ORDER BY bb.s
; 

Answer 14

谢谢hebo2005,
mantisXF 
谢谢大家
真的很感谢大家