oracle和DB2递归的例子

原始表: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