PostgreSQL查询- AVG()函数在临时表外

Question

我目前的代码如下:

SELECT num
  FROM (
        SELECT ... Code that returns the table I would expect ...
  ) table_name
WHERE num > (SELECT AVG(num) FROM table_name);

当前查询将拉出一个错误:error: relation "table_name" does not exist.

为什么会发生这种情况?

正如我在代码中所说的，我可以从括号内复制select语句:

SELECT ... Code that returns the table I would expect ...

，它将返回一个表，这是我所期望的，它包含一个列称为'num'。

作为旁注，当我给表一个名称(table_name在这种情况下)，它在SQL中被称为什么?我在标题中称之为临时表?如果不知道这个问题叫什么，就很难找到解决办法。

谢谢,卡梅伦

Answer 1

解决这个问题的一个方法是使用cte。

with table_name as
(SELECT ... Code that returns the table I would expect ...)
,avg_num as (select avg(num) as avgnum from table_name)
select t.num 
from table_name t join avg_num a
on t.num > a.avgnum;

Answer 2

另一个解决方案是使用窗口函数:

SELECT num
FROM (SELECT num, AVG(num) OVER () as avgnum
      FROM . . .    Code that returns the table I would expect ...
     ) table_name
WHERE num > avgnum;