ORACLE 树形数据求助

Question

参考下，和你的问题基本是一致的
http://bbs.csdn.net/topics/390219233

Answer 1

select t1.r_name,
       sum(decode(decode(t1.r_name, t1.name, 0, t1.ismemo),
                  1,
                  0,
                  nvl(t2.plan_val, 0))),
       t2.dept_id
  from (select basic.*, connect_by_root(name) r_name
          from basic
        connect by prior id = parent_id) t1,
       basic_record t2
 where t1.id = t2.basic_id
 group by t1.r_name, t2.dept_id
 order by dept_id, r_name

Answer 2

SELECT A.NAME,
(
  SELECT SUM(plan_val) FROM basic_record 
  WHERE DEPT_ID=B.DEPT_ID 
  AND basic_id IN (SELECT ID FROM basic 
    WHERE ismemo<>1 
    CONNECT BY PRIOR ID=parent_id
    START WITH ID=A.ID)
) plan_val,
B.DEPTNO
FROM basic A,(SELECT DISTINCT dept_id  FROM basic_record) B
WHERE A.ismemo<>1
ORDER BY B.DEPT_ID,A.ID

Answer 3

select t1.r_name,
       sum(decode(decode(t1.r_name, t1.name, 0, t1.ismemo),
                  1,
                  0,
                  nvl(t2.plan_val, 0))),
       t2.dept_id
  from (select basic.*, connect_by_root(name) r_name
          from basic
        connect by prior id = parent_id) t1,
       basic_record t2
 where t1.id = t2.basic_id
 group by t1.r_name, t2.dept_id
 order by dept_id, r_name
感觉有问题,这样查询出来的记录数和basic_record表的记录数是一致的,比结果集的记录要少
关联条件是否应该改为t1.id = t2.basic_id(+)

Answer 4

INSERT INTO basic_record VALUES(1,1,6,100);
INSERT INTO basic_record VALUES(2,1,5,200);
INSERT INTO basic_record VALUES(3,1,3,500);
INSERT INTO basic_record VALUES(4,2,6,300);
INSERT INTO basic_record VALUES(5,2,5,100);
INSERT INTO basic_record VALUES(6,2,3,300);
commit;填报表填报SQL

Answer 5

select t1.r_name,
       sum(decode(decode(t1.r_name, t1.name, 0, t1.ismemo),
                  1,
                  0,
                  nvl(t2.plan_val, 0))),
       t2.dept_id
  from (select basic.*, connect_by_root(name) r_name
          from basic
        connect by prior id = parent_id) t1,
       basic_record t2
 where t1.id = t2.basic_id
 group by t1.r_name, t2.dept_id
 order by dept_id, r_name
感觉有问题,这样查询出来的记录数和basic_record表的记录数是一致的,比结果集的记录要少
关联条件是否应该改为t1.id = t2.basic_id(+)

Answer 6

多写了个条件，去掉就可以了，他说不参与计算，我以为结果集中也不需要了呢
SELECT A.NAME,
(
  SELECT SUM(plan_val) FROM basic_record 
  WHERE DEPT_ID=B.DEPT_ID 
  AND basic_id IN (SELECT ID FROM basic 
    WHERE ismemo<>1 
    CONNECT BY PRIOR ID=parent_id
    START WITH ID=A.ID)
) plan_val,
B.DEPTNO
FROM basic A,(SELECT DISTINCT dept_id  FROM basic_record) B
ORDER BY B.DEPT_ID,A.ID

Answer 7

basic3的结果不对，他要的结果是basic3的结果500和300,你的sql执行出来为null

Answer 8

select t1.r_name,
       sum(decode(decode(t1.r_name, t1.name, 0, t1.ismemo),
                  1,
                  0,
                  nvl(t2.plan_val, 0))),
       t2.dept_id
  from (select basic.*, connect_by_root(name) r_name
          from basic
        connect by prior id = parent_id) t1,
       basic_record t2
 where t1.id = t2.basic_id
 group by t1.r_name, t2.dept_id
 order by dept_id, r_name
你的这个终于看明白了，执行速度应该比我这个要快，佩服

Answer 9

basic3的结果不对，他要的结果是basic3的结果500和300,你的sql执行出来为null
 ismemo<>1 这个条件的问题
让楼主用你的语句吧，懒得改了，呵呵