oracle中如何避免重复？

Question

SELECT title, retail, (SELECT AVG(retail)
                 FROM   books) 
FROM   books
WHERE  retail < (SELECT AVG(retail)
                 FROM   books) 

有没有办法缩短其中之一 (SELECT AVG(retail) 从书籍）到别名？

Answer 1

试试这个：

SELECT *
  FROM (
    SELECT title, retail, AVG(retail) OVER() avg_retail
    FROM   books
    )
WHERE  retail < avg_retail

或使用WITH：

WITH dat AS
(
    SELECT title, retail, AVG(retail) OVER() avg_retail
    FROM   books
)
SELECT *
  FROM dat
 WHERE  retail < avg_retail

Answer 2

您可以使用 WITH 子句进行重构，也许像这样：

With avg_retail as
    (SELECT AVG(retail) the_avg
    FROM   books) 
SELECT title, retail, avg_retail.the_avg
from books, avg_retail
where books.retail < avg_retail.the_avg;