sql - 在 Oracle 分层查询中访问祖先（CONNECT BY PRIOR）

Question

我目前正在为一些项目制定材料清单，但遇到了问题。

我有一个包含以下列的源表：

• 部分
• 零件
• 数量

自然还有其他列，不过为了简单起见……

组件当然也可以是组件。

以下查询将产生我对零件及其子组件的分层视图

SELECT
    Part,
    Component,
    qty

FROM
    sourceTable

CONNECT BY
    PRIOR Component = Part

START WITH
    Part = '<Part number here>'

不过有一个警告。源表还包含未消耗的项目，（替代项目等）在这种情况下，数量列设置为 0

但是，所有后续部分均未设置为 0。

因此，我可以通过访问 PRIOR 元素 qty 在子结构上解决这个问题。

SELECT
    Part,
    Component,
    qty,
    PRIOR qty * qty AS QTY_PER_MAIN_ASSEMBLY

FROM
    sourceTable

CONNECT BY
    PRIOR Component = Part

START WITH
    Part = '<Part number here>'

我将如何在层次结构中的子组件或较低级别上进行此操作？

在这种情况下，我基本上想知道如何访问 n 级 PRIOR qty

- - - - - - - - - - - - - - 编辑 - - - - - - - - - - - -----

在一些谷歌搜索的帮助下弄清楚了

通过在 qty 列上使用 SYS_CONNECT_BY_PATH，我能够构建一个包含 qty 层次结构的字符串

在此之后，我必须构建一个函数，该函数将这个字符串拆分为单独的值，并将这些值相乘

CREATE OR REPLACE FUNCTION eval (expr_in IN VARCHAR2)
  RETURN NUMBER
  AUTHID CURRENT_USER
  DETERMINISTIC
  RESULT_CACHE
IS
  PRAGMA UDF;
  v_res NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || expr_in || ' FROM DUAL' INTO v_res;
  RETURN v_res;
END eval;

使用上述函数，我只需将其插入到我的选择查询中

EVAL(SUBSTR( SYS_CONNECT_BY_PATH( TO_CHAR(qty), '*' ) , 2)) AS QTY_PER_MAIN_ASSEMBLY

标签： sqloraclerecursionhierarchicalconnect-by

Answer 1

您找到了一个解决方案，但是有一个替代方案，不需要函数，递归查询：

with t(part, component, qty, qty_per_main_assembly) as (
    select part, component, qty, qty 
      from sourcetable 
      where part = '<Part number here>'
    union all
    select s.part, s.component, s.qty, t.qty_per_main_assembly * s.qty
      from t join sourcetable s on t.component = s.part)
select * from t

^{dbfiddle 示例}