创建一个包含三个表的嵌套子查询

Question

我有下面提到的3个表。每个部分有两个与其相关的扫描。

首先，我需要找到每个社区的部分，这是我用下面的代码完成的。然后，我需要从每个邻域的每个部分的扫描中找到总容量(sum(cap_a+cap_b((。

想要的是一个选择，列出社区和社区中各部分的总容量。

我一直在研究子查询，但我不知道这个问题的语法。

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-subquery/

neighborhood (id,name,geom) 
sections (section,geom
scans (section,cap_a,cap_b)
SELECT neighborhood.name, sections.section
FROM neighborhood,sections
WHERE ST_WITHIN(section.geom,neighborhood.geom)
SELECT section,SUM(cap_a+cap_b) AS capacity
FROM scans

到目前为止，我已经提出了这个查询，但它没有给出我想要的正确结果。

SELECT
neighborhood.id,
neighborhood .name,
capcity_of_neighborhood
FROM    
neighborhood,
section,
scan
JOIN (
SELECT section,SUM(cap_a+cap_b) AS capacity
FROM scans
GROUP BY section
) AS capcity_of_neighborhood 
ON capcity_of_neighborhood.section = scans.section
WHERE 
ST_WITHIN(section.geom,neighborhood.geom)
GROUP BY 1,2,3
;

Answer 1

希望我能正确理解您的查询。试试这个：

SELECT ne.id
, ne.name,
, sc.capacity_total 
FROM neighborhood ne
JOIN section se
ON ST_WITHIN(se.geom, ne.geom)
JOIN (
SELECT section
, SUM(cap_a+cap_b) AS capacity_total
FROM scans
GROUP 
BY section
) AS sc
ON sc.section = se.section
;