WITH子句SQL语句报错：找不到display_name_fid表条目

场景还原 #

首先执行这条SQL查询得到目标fid值：

SELECT fid 
FROM field 
WHERE name = 'DisplayName' AND sid = 8;

查询结果为1行1列，列名fid，值为998。

随后尝试用WITH子句重构查询，写出如下语句：

WITH display_name_fid AS 
(
    SELECT fid 
    FROM field 
    WHERE name = 'DisplayName' AND sid = 8
)
SELECT * 
FROM sval 
WHERE display_name_fid.fid = 998;

执行后触发错误：

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "display_name_fid"
Position: 116

而直接执行硬编码fid=998的SQL可以正常得到结果：

SELECT * 
FROM sval 
WHERE fid = 998;

问题原因 #

你定义了CTE（WITH子句中的临时表display_name_fid），但主查询的FROM子句里根本没引用这个临时表，却在WHERE条件里直接使用display_name_fid.fid，数据库找不到这个表的关联关系，因此报错。

正确写法 #

根据你的需求，有两种可行的修正方式：

方式1：JOIN关联CTE

适合CTE可能返回多个fid值的场景：

WITH display_name_fid AS 
(
    SELECT fid 
    FROM field 
    WHERE name = 'DisplayName' AND sid = 8
)
SELECT s.* 
FROM sval s
JOIN display_name_fid d ON s.fid = d.fid;

方式2：子查询引用CTE值

适合确定CTE只返回单个fid值的场景：

WITH display_name_fid AS 
(
    SELECT fid 
    FROM field 
    WHERE name = 'DisplayName' AND sid = 8
)
SELECT * 
FROM sval 
WHERE fid = (SELECT fid FROM display_name_fid);

两种写法都能替代硬编码998的方式，实现动态获取fid并过滤sval表的效果。

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