sql - 将值传递给 PostgreSQL 中的子查询

Question

我正在尝试按名称收集居住在用户指定州周围州的所有家庭。正如您在下面看到的。但是，当我执行代码时出现此错误，我不确定如何解决它:

错误:

ERROR:  syntax error at or near "x"
LINE 13:  families WHERE state_name IN x.names and family_name...

代码:

with x as (
    SELECT 
        bordering_states_names as names
    FROM 
        states 
    WHERE 
        state_name = 'Ohio' 
)
SELECT 
    family_name,
    state_name
FROM 
    families WHERE state_name IN x.names and family_name IS NOT NULL
LIMIT 20

在这种情况下，bordering_states_names 的值如下所示:

{"Ohio", "Indiana", "West Virginia", "Pennsylvania"}

Answer 1

表 x 应该与下面查询的 FROM 部分 一起存在，否则 x 可能没有意义。

因此，在以下情况下加入这些表(families 和 x)可能是一种选择:

WITH x AS (
    SELECT 
        bordering_states_names as names
    FROM 
        states 
    WHERE 
        state_name = 'Ohio' 
)
SELECT f.family_name, f.state_name
  FROM families f inner join x
    ON ( f.state_name = x.names ) 
   AND family_name IS NOT NULL
 LIMIT 20;