ORA-00904: 子查询中无效的标识符

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

当我使用EO.ENTITY_ID时，它会产生“ORA-00904: "EO"."ENTITY_ID": invalid identifier”的错误。然而，当我将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

- ako

---

筛选条件没有起到任何作用。您正在计算存在于“entity_ownership”中的行数 - 但是您正在从同一张表中进行选择。对于原始表中的每一行，“count（*）”必须至少为1。 - Gordon Linoff

我给出了一个简化的例子，是我的错。请查看更新版本。 - ako

Answer 1

基本上，一个相关子查询可以访问相关联的表。但是，当存在一个内部子查询时，内部查询将首先尝试执行...因此，条件中的其他表无法访问，因为它们在那时不可用。

理解这一点的快捷方式是..如其他答案中所述。

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
)

- Maheswaran Ravisankar

Answer 2

我认为你可以使用exists来做到这一点：

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 
    );

- Gordon Linoff

---

非常感谢，您的回答很有帮助。我为您点赞。 - ako