sql - 在另一个 JOINS 子查询中引用来自 JOIN 的列

Question

我正在尝试使用连接、子查询和聚合函数编写一些复杂的 POSTGRES 查询。一切正常，但有一件事。这是代码:

SELECT 

table1.id AS id1,
table1.name AS name,
table1.table2_fkey AS id2
table2.name AS name2,
table2.id,
month_sum

FROM table1 
LEFT JOIN table2 ON (table1.table2_fkey = table2.id) 
INNER JOIN (
    SELECT sum(months) AS month_sum FROM (
        SELECT (month_1 + month_2 + month_3 + month_4 + month_5 + month_6 + month_7 + month_8 + month_9 + month_10 + month_11 + month_12) as months FROM table2_places_years WHERE table2_places_fkey IN (
            SELECT id FROM table2_places WHERE table2_people_fkey IN (
                SELECT id FROM table2_people WHERE table2_fkey = table2.id
            )
        ) 
    ) AS months
) AS month_sum ON (table2.id = id2)

(我简化了列和表的名称)

问题是第二个 JOIN 中的最后一个子查询无法访问第一个连接中的 table2，因此它会抛出错误。如果我从最后一个子查询中删除 where 子句，查询运行正常，但会得到“table2_places_years”中所有行的总和。我只想从与 table2 中当前条目关联的行中求和(通过另外三个表与 fkeys 链接)。

有没有办法“让子查询知道”来自第一个连接子句的列？

提前致谢!

注意:这段代码可能行不通，因为我做了一些简化。

Answer 1

如果数据允许，使用连接重写查询。然后它看起来像这样:

join (
select table2_fkey, sum(…) …
from table2_places_years
join table2_places …
join table2_people …
group by table2_fkey
) month_sum on table2_fkey = table2.id

如果没有，也可以将相关子查询放在select语句中:

select …,
      (
      select sum(…) …
      ) month_sum
from table1 left join table2 …

最后一种方法可能是横向连接。它可能看起来像这样:

SELECT 

table1.id AS id1,
table1.name AS name,
table1.table2_fkey AS id2
table2.name AS name2,
table2.id,
month_sum

FROM table1 
LEFT JOIN table2 ON (table1.table2_fkey = table2.id),
LATERAL (
    SELECT sum(months) AS month_sum FROM (
        SELECT (month_1 + month_2 + month_3 + month_4 + month_5 + month_6 + month_7 + month_8 + month_9 + month_10 + month_11 + month_12) as months FROM table2_places_years WHERE table2_places_fkey IN (
            SELECT id FROM table2_places WHERE table2_people_fkey IN (
                SELECT id FROM table2_people WHERE table2_fkey = table2.id
            )
        ) 
    ) AS months
) month_sum

http://www.depesz.com/2012/08/19/waiting-for-9-3-implement-sql-standard-lateral-subqueries/