where 子句無法訪問子查詢的名稱

SELECT
 NAME,
 (SELECT COUNT(1) FROM MOVIERENTED WHERE MOVIERENTED.MOVIEID=MOVIE.MOVIEID) AS RENTCOUNT
FROM MOVIE
ORDER BY RENTCOUNT;

此數據庫中的某些電影從未租過，如何僅選擇子查詢之外已租借的電影？

我試過WHERE RENTCOUNT > 0在FROM MOVIE聲明之後添加。

ORA-00904: “RENTCOUNT”: 無效標識符

簡單地使用內部聯接（您目前使用標量子查詢的查詢類似於外部聯接）：

SELECT
 MOVIE.NAME,
 COUNT(*) AS RENTCOUNT
FROM MOVIE 
JOIN MOVIERENTED 
ON MOVIERENTED.MOVIEID=MOVIE.MOVIEID
GROUP BY MOVIE.NAME, MOVIE.MOVIEID
ORDER BY RENTCOUNT;

一種選擇是添加嵌套級別

select *
 from (SELECT NAME,
              (SELECT COUNT(1) 
                 FROM MOVIERENTED 
                WHERE MOVIERENTED.MOVIEID=MOVIE.MOVIEID) AS RENTCOUNT
         FROM MOVIE) optional_alias
where rentcount > 0
ORDER BY RENTCOUNT;

引用自：https://dba.stackexchange.com/questions/148798