经理的薪金累积(甲骨文DB2 CONNECT_BY)

Question

我们有来自scott.emp表的数据：

选择empno，ename，mgr，sal，从emp命令中选择empno；

EMPNO|ENAME     |  MGR|       SAL
-----|----------|-----|----------
 7369|SMITH     | 7902|       800
 7499|ALLEN     | 7698|      1600
 7521|WARD      | 7698|      1250
 7566|JONES     | 7839|      2975
 7654|MARTIN    | 7698|      1250
 7698|BLAKE     | 7839|      2850
 7782|CLARK     | 7839|      2450
 7788|SCOTT     | 7566|      3000
 7839|KING      |     |      5000
 7844|TURNER    | 7698|      1500
 7876|ADAMS     | 7788|      1100
 7900|JAMES     | 7698|       950
 7902|FORD      | 7566|      3000
 7934|MILLER    | 7782|      1300

14 rows selected.

然后，我想列出emp的层次结构以及工资积累，它返回如下行：

DESKRIPSI | EMPNO|   MGR|  AMOUNT
----------|------|------|--------
KING      |  7839|      |  29.025
.JONES    |  7566|  7839|  10.875
..SCOTT   |  7788|  7566|   4.100
...ADAMS  |  7876|  7788|   1.100
..FORD    |  7902|  7566|   3.800
...SMITH  |  7369|  7902|     800
.BLAKE    |  7698|  7839|   9.400
..ALLEN   |  7499|  7698|   1.600
..WARD    |  7521|  7698|   1.250
..MARTIN  |  7654|  7698|   1.250
..TURNER  |  7844|  7698|   1.500
..JAMES   |  7900|  7698|     950
.CLARK    |  7782|  7839|   3.750
..MILLER  |  7934|  7782|   1.300

14 rows selected.

对于Oracle ，其方法如下：

WITH pohon
     AS (SELECT
           DISTINCT CONNECT_BY_ROOT empno parent_id, empno AS id
         FROM
           emp
         CONNECT BY
           PRIOR empno = mgr),
     trx
     AS (SELECT
           pohon.parent_id, SUM (tx.sal) AS amount
         FROM
           pohon JOIN emp tx ON pohon.id = tx.empno
         GROUP BY
           pohon.parent_id)
SELECT
  LPAD (r0.ename, LENGTH (r0.ename) + LEVEL * 1 - 1, '.') AS deskripsi,empno, mgr,
  trx.amount
FROM
  emp r0 JOIN trx ON r0.empno = trx.parent_id
START WITH
  r0.mgr IS NULL
CONNECT BY
  r0.mgr = PRIOR r0.empno
;

如何在DB2 RDBMS中获得相同的结果？

致以问候。

Answer 1

有两种方法，要么启用副条款

或者按照此页面上的示例使用SQL标准递归SQL。

例2:总结爆炸第二个例子是总结爆炸。这里提出的问题是，建造“01”部分所需的每一部分的总数量是多少。与单级爆炸的主要区别在于对总量的要求。第一个示例指出了在需要该部件时所需的子部件的数量。它没有说明构建“01”部分需要多少个子部件。

WITH RPL (PART, SUBPART, QUANTITY) AS
   (
      SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
       FROM PARTLIST ROOT
       WHERE ROOT.PART = '01'
    UNION ALL
      SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
       FROM RPL PARENT, PARTLIST CHILD
       WHERE PARENT.SUBPART = CHILD.PART
   )
SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
 FROM RPL
  GROUP BY PART, SUBPART
  ORDER BY PART, SUBPART;

Answer 2

下面的语句返回所需的结果。按原样运行。

WITH EMP (EMPNO, ENAME, MGR, SAL) AS
(
VALUES
  (7369, 'SMITH     ', 7902,  800)
, (7499, 'ALLEN     ', 7698, 1600)
, (7521, 'WARD      ', 7698, 1250)
, (7566, 'JONES     ', 7839, 2975)
, (7654, 'MARTIN    ', 7698, 1250)
, (7698, 'BLAKE     ', 7839, 2850)
, (7782, 'CLARK     ', 7839, 2450)
, (7788, 'SCOTT     ', 7566, 3000)
, (7839, 'KING      ', NULL, 5000)
, (7844, 'TURNER    ', 7698, 1500)
, (7876, 'ADAMS     ', 7788, 1100)
, (7900, 'JAMES     ', 7698,  950)
, (7902, 'FORD      ', 7566, 3000)
, (7934, 'MILLER    ', 7782, 1300)
)
, C (LVL, EMPNO, MGR, ENAME, SAL, CHAIN) AS 
(
SELECT
  0 AS LVL, EMPNO, MGR, ENAME, SAL
, CAST('|' || TRIM(CHAR(EMPNO)) || '|' AS VARCHAR(1024)) AS CHAIN
FROM EMP C 
WHERE NOT EXISTS (SELECT 1 FROM EMP P WHERE P.EMPNO = C.MGR)
  UNION ALL
SELECT C.LVL + 1 AS LVL, E.EMPNO, E.MGR, E.ENAME, E.SAL
, C.CHAIN || TRIM(CHAR(E.EMPNO)) || '|' AS CHAIN
FROM C, EMP E
WHERE E.MGR = C.EMPNO
)
SELECT 
  REPEAT('.', LVL) || ENAME AS DESKRIPSI
, EMPNO
, MGR
, (SELECT SUM(SAL) FROM C C2 WHERE C2.CHAIN LIKE C1.CHAIN || '%') AS AMOUNT
FROM C C1
ORDER BY CHAIN;