数据查询的问题
oracle的查询

表A:
    codeId   数量1   数量2   数量3
    001             100     200     300
    002             200     300     400
    003             100     500     200

表B:
    codeID   parentid
    001        
    002         001
    003         001
    004         002


意思大概是这样
表B是一个父子的对应表
如果parentid为空则为根

表A中放的codeid和表B是对应起来的


现在要求:
要查询
001下面所有的表B的数据总和[数据和为数量1+数量2+数量3]
其他当然包括表B中codeid为002/003的,因为这些都是属于001的
其他的类推


------解决方案--------------------
范例:
CREATE TABLE TBL_B(CODEID VARCHAR2(3), PARENTID VARCHAR2(3));

INSERT INTO TBL_B
SELECT '001 ',NULL FROM DUAL UNION ALL
SELECT '002 ', '001 ' FROM DUAL UNION ALL
SELECT '003 ' , '001 ' FROM DUAL UNION ALL
SELECT '004 ' , '002 ' FROM DUAL;

CREATE TABLE TBL_A(CODEID VARCHAR2(3), NUM1 INT, NUM2 INT, NUM3 INT);

INSERT INTO TBL_A
SELECT '001 ', 100, 200 ,300 FROM DUAL UNION ALL
SELECT '002 ', 200 , 300 , 400 FROM DUAL UNION ALL
SELECT '003 ', 100 ,500, 200 FROM DUAL;

SELECT SUM(NUM1+NUM2+NUM3)
FROM TBL_A
WHERE EXISTS(
SELECT 1 FROM TBL_B
WHERE CODEID = TBL_A.CODEID
START WITH PARENTID IS NULL
CONNECT BY PRIOR CODEID = PARENTID)
/
------解决方案--------------------
select sum(a.数量1+a.数量2+a.数量3) from a
where exists(
select b.codeID from b
where a.codeID=b.parentid
and b.parentid= '001 '
or
(b.codeID= '001 ' and b.parentid is null)
)

没测试.对于楼上的START WITH PARENTID IS NULL CONNECT BY PRIOR CODEID = PARENTID
没用过,希望解释下!谢谢
------解决方案--------------------
SELECT SUM(NUM1+ NUM2 + NUM3)
FROM TBL_A A
WHERE A.CODEID IN (SELECT CODEID
FROM TBL_B B
WHERE B.CODEID = '001 '
OR B.PARENTID = '001 '
)
------解决方案--------------------
既然004屬于002，002屬于001，那004為什么不屬于001？
------解决方案--------------------
SELECT SUM(NUM1+NUM2+NUM3)
FROM TBL_A
WHERE EXISTS(
SELECT 1 FROM TBL_B
WHERE CODEID = TBL_A.CODEID
START WITH CODEID = '001 '
CONNECT BY PRIOR CODEID = PARENTID)

如果004 也属于001的话用上面的查询