Heroku Dataclips查询变量替换失效问题求助

我来帮你搞定这个问题～你之前的写法思路是对的，但漏了关键一步：你定义的CTE vars 没有被主查询引用，所以数据库找不到这个“临时表”，才会抛出missing FROM-clause entry for table "vars"的错误。

问题根源 #

CTE（WITH子句）定义的是一个临时结果集，你必须在主查询的FROM子句里包含它，才能在查询中引用它的字段。你之前的主查询只从taxes表取数据，完全没提到vars，数据库当然不知道它存在啦。

修正后的查询写法 #

因为vars里只有一行数据（我们定义的参数），用CROSS JOIN把它和taxes表关联是最安全的——不会产生多余的行，只是给每一行taxes数据带上我们的变量值。这里我还加了表别名，让SQL更清晰：

WITH vars AS ( 
  SELECT '2018-01-07'::DATE AS calcdate, 12345 AS salary 
)
select t.* 
from taxes t
cross join vars v
where t.country_alpha3='USA' 
  and t.year='2018' 
  and t.active=true 
  and t.subdivision_code='US-MEDI' 
  and t.local_code is null 
  and t.start_date <= v.calcdate 
  and t.end_date >= v.calcdate 
  and t.lower_amount_cents <= v.salary 
  and t.upper_amount_cents >= v.salary;

你也可以用更简洁的隐式交叉连接写法（效果和上面完全一样）：

WITH vars AS ( 
  SELECT '2018-01-07'::DATE AS calcdate, 12345 AS salary 
)
select t.* 
from taxes t, vars v
where t.country_alpha3='USA' 
  and t.year='2018' 
  and t.active=true 
  and t.subdivision_code='US-MEDI' 
  and t.local_code is null 
  and t.start_date <= v.calcdate 
  and t.end_date >= v.calcdate 
  and t.lower_amount_cents <= v.salary 
  and t.upper_amount_cents >= v.salary;

额外优化点 #

我把DATE(vars.calcdate)改成了直接用v.calcdate，因为在CTE里已经把calcdate定义成DATE类型了（用::DATE强制转换），这样可以避免重复的类型转换，让查询更高效。

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