具有父子目录关系的数据统计sql语句实现

Question

本帖最后由 eddy45 于 2010-10-18 16:06:14 编辑

Answer 1

我给你个例,需要用到connect by,你自己看看.
--1.Hierarchical Queries: START WITH and CONNECT BY PRIOR clauses 
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
          1          0 James      Smith
          2          1 Ron        Johnson
          3          2 Fred       Hobbs
          5          2 Rob        Green
          4          1 Susan      Jones
          6          4 Jane       Brown
          9          6 Henry      Heyson
          7          4 John       Grey
          8          7 Jean       Blue
         10          1 Kevin      Black
         11         10 Keith      Long
         12         10 Frank      Howard
         13         10 Doreen     Penn13 rows selected.--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id;     LEVEL EMPLOYEE
---------- -------------------------
         1  Kevin Black
         2    Keith Long
         2    Frank Howard
         2    Doreen Penn--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
       last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;     LEVEL EMPLOYEE                      SALARY
---------- ------------------------- ----------
         3      Rob Green                 40000
         3      Jane Brown                45000
         4        Henry Heyson            30000
         3      John Grey                 30000
         4        Jean Blue               29000
         3      Keith Long                50000
         3      Frank Howard              45000
         3      Doreen Penn               470008 rows selected.

Answer 2

只是查下一层的话直接用select * from table where parentid=本层id循环取数据就可以了.

Answer 3

那你怎么知道本层目录下每个子目录所包含的keyName呢

Answer 4

树状结构的构造，只需要看你关联性了，connect by 循环递归求解子孙节点