原始表:CREATE TABLE departments (deptid INT,
deptname VARCHAR(20),
empcount INT,
superdept INT)
查询某个部门的所有子部门的总人数
--------------------------------------------------------------------------------
Oracle:SELECT sum(empcount) FROM STRUCREL
CONNECT BY PRIOR superdept = deptid START WITH deptname = '部门名称';
--------------------------------------------------------------------------------
DB2:WITH temptab(deptid, empcount, superdept) AS
( SELECT root.deptid, root.empcount, root.superdept FROM departments root WHERE deptname='Production' UNION ALL SELECT sub.deptid, sub.empcount, sub.superdept FROM departments sub, temptab super WHERE sub.superdept = super.deptid ) SELECT sum(empcount) FROM temptab