(Oracle)关联的子查询使用情况

Question

下面的子查询query1运行良好。但是，当我把equi条件放在query2之类的嵌套子句中时，它显示了错误ORA-00904。这是相关子查询的错误使用还是因为其他原因？

--Query1: It shows expected result.
SELECT
O.ENAME
O,SAL
,(SELECT COUNT(*)
FROM SCOTT.EMP I
WHERE I.SAL>O.SAL  --correlated to outer
) AS RESULT
from SCOTT.EMP O;

--Query2:ORA-00904: "O"."SAL": invalid identifier shows. How to modify to use correlated subquery?
SELECT
O.ENAME
O,SAL
,(
WITH TEMP AS 
(
SELECT COUNT(*)
FROM SCOTT.EMP I
WHERE I.SAL>O.SAL   --I have put equi condistion here 
)
SELECT * FROM TEMP
)  AS RESULT
from SCOTT.EMP O;

Answer 1

我认为第二个选项错误地使用了相关子查询，不是因为比较，而是因为使用了with子句我想记住，您应该尽可能避免相关的子查询。

WITH子句或子查询分解子句可以作为内联视图处理，也可以作为临时表解析。后者的优点是，重复引用子查询可能更有效，因为数据很容易从临时表中检索，而不是由每个引用重新查询。

在第二个查询的第三列中，您希望从内联视图中获得结果。问题是内联视图的解析是独立完成的，因此不能引用外部查询中的任何内容。

SQL> create table emp ( ename varchar2(10) , sal number ) ;
Table created.
SQL> insert into emp values ( 'AAA' , 1000 ) ;
insert into emp values ( 'BBB' , 1000 ) ;
insert into emp values ( 'CCC' , 1000 ) ;
insert into emp values ( 'DDD' , 1000 ) ;
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> select * from emp ;
ENAME             SAL
---------- ----------
AAA              1000
BBB              1000
CCC              1000
DDD              1000

要编写具有内联视图的查询，必须在外部查询中进行筛选

SELECT
O.ENAME
O,SAL
,(
WITH TEMP AS 
(
SELECT * FROM EMP 
)
SELECT count(*) FROM TEMP t WHERE t.SAL>O.SAL
)  AS RESULT
from EMP O;
O                 SAL     RESULT
---------- ---------- ----------
AAA              1000          0
BBB              1000          0
CCC              1000          0
DDD              1000          0

Answer 2

已经解释过第二个查询没有正确使用with。

但是，让我建议您的查询可以更简单、更有效地措辞。对于每个员工，您需要计算有多少员工的工资更高。窗口功能在这里是可行的：

select e.*, rank() over(order by salary desc) - 1 result
from scott.emp e