with PART as
(select ITEMS_TREE.OBJECT_ID1 as object_id ,ITEMS_TREE.OBJECT_ID2 as ParObject_id from ITEMS_TREE
start with ITEMS_TREE.OBJECT_ID1 = 157 connect by prior ITEMS_TREE.OBJECT_ID2 = ITEMS_TREE.OBJECT_ID1)select A.object_id,A.ParObject_id,B.object_id2 as zuObject from PART A
join PART B on A.OBJECT_ID2 = B.OBJECT_ID1
ITEMS_TREE是一个结构树表,有多棵树,由自身ID和父ID构成。第一句SELECT根据需要拎出一棵树。我希望得到这棵树每个节点的的祖和先祖ID。上面只写到获取祖,查询不出。报B无定义什么的。是一个自查询,不知道改怎么处理,请高手指点。
with as 的别名不能再别名了?不能再用了?有没其他解决方案?
(select ITEMS_TREE.OBJECT_ID1 as object_id ,ITEMS_TREE.OBJECT_ID2 as ParObject_id from ITEMS_TREE
start with ITEMS_TREE.OBJECT_ID1 = 157 connect by prior ITEMS_TREE.OBJECT_ID2 = ITEMS_TREE.OBJECT_ID1)select A.object_id,A.ParObject_id,B.object_id2 as zuObject from PART A
join PART B on A.OBJECT_ID2 = B.OBJECT_ID1
ITEMS_TREE是一个结构树表,有多棵树,由自身ID和父ID构成。第一句SELECT根据需要拎出一棵树。我希望得到这棵树每个节点的的祖和先祖ID。上面只写到获取祖,查询不出。报B无定义什么的。是一个自查询,不知道改怎么处理,请高手指点。
with as 的别名不能再别名了?不能再用了?有没其他解决方案?
你的错误在于
以part为名称的表实上只有两列了(object_id,ParObject_id),
引用原表ITEMS_TREE中的其它列是不正确的
实际上是这样:
WITH PART AS
(SELECT ITEMS_TREE.OBJECT_ID1 AS object_id,
ITEMS_TREE.OBJECT_ID2 AS ParObject_id
FROM ITEMS_TREE
START WITH ITEMS_TREE.OBJECT_ID1 = 157
CONNECT BY PRIOR ITEMS_TREE.OBJECT_ID2 = ITEMS_TREE.OBJECT_ID1)
SELECT A.object_id, A.ParObject_id, B.ParObject_id AS zuObject
FROM PART A
JOIN PART B
ON A.ParObject_id = B.object_id;