sql - ORA-00904 : invalid identifier in subquery

Question

为什么下面的查询在 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

Answer 1

如果您按照基础知识进行，则相关 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
)