分级误差

Question

这是我的代码：

    with t(branch_id, branch_code, branch, parent_branch_id, level) as
  (
    Select parent_b.BRANCH_ID, parent_b.BRANCH_CODE,
           parent_b.BRANCH, parent_b.PARENT_BRANCH_ID, 0 as level
    from table parent_b
    Where parent_b.branch_Id is null--= 1
    UNION ALL
    Select child_b.BRANCH_ID, child_b.BRANCH_CODE,
           child_b.BRANCH, child_b.PARENT_BRANCH_ID, (t.level+1)
    from table child_b 
    INNER JOIN t parent
       ON parent.branch_id = child_b.parent_branch_id
  )
  select * from t

我在输出时会出错。错误在哪里，我找不到。

错误：

多部分标识符"t.level“无法绑定。

Answer 1

将t.level更改为parent.level。您已将CTE的别名命名为“家长”。你得用这个。

  with t(branch_id, branch_code, branch, parent_branch_id, level) as
  (
    Select parent_b.BRANCH_ID, parent_b.BRANCH_CODE,
           parent_b.BRANCH, parent_b.PARENT_BRANCH_ID, 0 as level
    from table parent_b
    Where parent_b.branch_Id is null--= 1
    UNION ALL
    Select child_b.BRANCH_ID, child_b.BRANCH_CODE,
           child_b.BRANCH, child_b.PARENT_BRANCH_ID, (parent.level+1)
    from table child_b 
    INNER JOIN t parent
       ON parent.branch_id = child_b.parent_branch_id
  )
  select * from t