sql - ORA-00904 : invalid identifier in subquery

为什么下面的查询在 oracle 中不起作用？

select * from ENTITY_OWNERSHIP EO
where
(select count (*)
    from (
      select USER_ID
      from ENTITY_OWNERSHIP
      where ENTITY_OWNERSHIP.ENTITY_ID = EO.ENTITY_ID
    )
) > 0

它产生“ORA-00904:“EO”。“ENTITY_ID”:无效标识符”。但是，当我将 EO.ENTITY_ID 替换为确切值时，例如 10181，它就可以工作了。

更新:
完整的查询如下所示:

select * from ENTITY_OWNERSHIP EO
where
(select count (*)
    from (
      select USER_ID
      from ENTITY_OWNERSHIP
      where ENTITY_OWNERSHIP.ENTITY_ID = EO.ENTITY_ID

      intersect

      select distinct group_id
      from USERS.GROUPS
      start with GROUP_ID in (select GROUP_ID from USERS.LK_GROUPS where USER_ID=10001)
      connect by prior PARENTGROUP_ID=GROUP_ID
    )
) > 0

最佳答案

如果你按照基础知识，一个 CORRELATED Subquery 可以访问相关表..但是当有一个 INNER subquery 时，INNER Query 将首先尝试执行......所以条件中的另一个表不能被访问，因为它们不是在那个时间点可用。
理解这一点的捷径是..如另一个答案中所述..

SELECT A.* FROM TABLE A
WHERE EXISTS
 (SELECT 'X' FROM TABLE B WHERE B.ID = A.ID)

现在，相关子查询可以访问 A。

select * from ENTITY_OWNERSHIP EO
where
EXISTS
(
      select USER_ID
      from ENTITY_OWNERSHIP
      where ENTITY_OWNERSHIP.ENTITY_ID = EO.ENTITY_ID

      intersect

      select distinct group_id
      from USERS.GROUPS
      start with GROUP_ID in (select GROUP_ID
                               from USERS.LK_GROUPS
                             where USER_ID=10001)
      connect by prior PARENTGROUP_ID=GROUP_ID
)

关于sql - ORA-00904 : invalid identifier in subquery，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21580705/