我有层次结构表有组织级的父子关系。另一个表有层次结构表中最低级别子级的帐户余额。我需要找到从上到下的所有层次结构。所有顶端父节点的顶端父节点都为“****”。请建议配置单元查询以解决此问题。
输入表:
层次结构表:
+---------------+----------------+
|parent_node_id | child_node_id |
+---------------+----------------+
| C1 | C11 |
+---------------+----------------+
| C11 | C12 |
+---------------+----------------+
| C12 | 123 |
+---------------+----------------+
| C12 | 456 |
+---------------+----------------+
| P1 | C1 |
+---------------+----------------+
| P1 | C2 |
+---------------+----------------+
| P2 | C3 |
+---------------+----------------+
Account Balance:
+-------+----------+
| node | balance |
+-------+----------+
| 123 | 100 |
+-------+----------+
| 456 | 150 |
+-------+----------+
| C2 | 400 |
+-------+----------+
| C3 | 200 |
+-------+----------+
输出数据:
扁平层次结构:
+---------------+-------------+---------+----------+----------+
|Parent_node_id | Level 1 | Level 2 | Level 2 | Level3 |
+---------------+-------------+---------+----------+----------+
| P1 | C1 | C11 | C12 | 123 |
+---------------+-------------+---------+----------+----------+
| P1 | C1 | C11 | C12 | 456 |
+---------------+-------------+---------+----------+----------+
| P1 | C2 | NULL | NULL | NULL |
+---------------+-------------+---------+----------+----------+
| P2 | C3 | NULL | NULL | NULL |
+---------------+-------------+---------+----------+----------+
从最低的孩子到最高的平衡
+-------+----------+
| node | balance |
+-------+----------+
| 123 | 100 |
+-------+----------+
| 456 | 150 |
+-------+----------+
| C12 | 250 |
+-------+----------+
| C11 | 250 |
+-------+----------+
| C1 | 250 |
+-------+----------+
| P1 | 650 |
+-------+----------+
| C2 | 200 |
+-------+----------+
| P2 | 200 |
+-------+----------+
暂无答案!
目前还没有任何答案,快来回答吧!