PostgreSQL报错：缺失max_table表的FROM子句条目，求问题排查

错误原因 #

你定义了CTE max_table 存储最大ID，但在current_data的查询中未将max_table加入FROM子句，数据库找不到该表的引用，因此抛出「缺失表"max_table"的FROM子句条目」的错误。

修复方案 #

有两种可行的修复方式：

方案1：交叉连接CTE表

由于max_table仅返回一行数据，交叉连接不会产生多余结果行，同时能让数据库访问到max_id：

with max_table as (select max(id) as max_id from home_history),
     current_data as (
         select Cast(h.created_at As date), count(h.id)
         from home_history h, max_table mt
         where h.id > (mt.max_id - 30 * 500000)
           and h.created_at >= CAST((now() + (INTERVAL '-30 day')) AS date)
           and h.created_at < CAST(now() AS date)
         group by Cast(h.created_at As date)
         order by Cast(h.created_at As Date)
     )
SELECT *
from current_data;

方案2：直接用子查询获取最大ID

无需单独定义CTE，直接在WHERE条件里嵌套子查询获取max_id，写法更简洁：

with current_data as (
         select Cast(created_at As date), count(id)
         from home_history
         where id > ((select max(id) from home_history) - 30 * 500000)
           and created_at >= CAST((now() + (INTERVAL '-30 day')) AS date)
           and created_at < CAST(now() AS date)
         group by Cast(created_at As date)
         order by Cast(created_at As Date)
     )
SELECT *
from current_data;

内容的提问来源于stack exchange，提问作者rmaleki