一个表中的内连接

Question

我有一个问题，我一直试图解决，但我现在感到卡住了，希望有人想要帮助我。

我有两张桌子：

parking_list (停车位及其属性列表)

area_zone (停车区)

我希望我的结果也是这样的：

area_number     total     sum_hcp
1               20          2
2               45          5
3               30          5

我可以生成每个区域内总停车位的总和，例如：

SELECT area.area_number,
      sum(parking.parking_spaces) AS total_sum
FROM my_schema.parking_list parking,
     my_schema.area_zones area
WHERE st_intersects(area.geom, parking.geom)
GROUP BY area.area_number

和每个区域的hcp停车量之和

SELECT area.area_number,
       sum(parking.parking_spaces) AS sum
FROM my_schema.parking_list parking,
     my_schema.area_zones area
WHERE st_intersects(area.geom, parking.geom) AND parking.hcp = 't' and parking.free = 't'
GROUP BY area.area_number as hcp_parking

但我需要加入它们，这样才能在视图中使用它。我尝试了几种不同的方法，但都没有成功。

下面是一个例子

SELECT area.area_number,
       sum(parking.parking_spaces) total_sum
FROM (
   my_schema.parking_list AS parking, my_schema.area_zones AS area
   WHERE st_intersects(area.geom, parking.geom)
) AS total
LEFT JOIN
(
  SELECT sum(parking.parking_spaces) AS sum_hcp
  FROM my_schema.parking_list AS parking2, my_schema.area_zones AS area2
  WHERE st_intersects(area.geom, parking.geom) AND parking.hcp = 't' and parking.free = 't'
) AS sum_hcp ON total.area_number = sum_hcp.area_number
GROUP BY area.area_number

有没有人能为我想做的事情指引正确的方向？

Answer 1

通过使用条件表达式和聚合sum函数，您应该能够在单个查询中获得这两个值：

SELECT 
  area.area_number,
  sum(parking.parking_spaces) AS total_sum
  sum(case when parking.hcp = 't' and parking.free = 't' 
           then parking.parking_spaces else 0 end
  ) as sum_hcp
FROM my_schema.parking_list parking,
     my_schema.area_zones area
WHERE st_intersects(area.geom, parking.geom) 
GROUP BY area.area_number;

如果您想要使用连接的派生表，可以像下面这样做，尽管前面的查询应该工作得更好，性能也更好：

SELECT area.area_number,
      sum(parking.parking_spaces) AS total_sum
      sum(coalesce(hcp_parking.parking_spaces,0)) as sum_hcp
FROM my_schema.parking_list parking,
     my_schema.area_zones area
LEFT JOIN (
    SELECT area.area_number,
         parking.parking_spaces
    FROM my_schema.parking_list parking,
         my_schema.area_zones area
    WHERE st_intersects(area.geom, parking.geom) AND parking.hcp = 't' and parking.free = 't'
) hcp_parking ON area.area_number = hcp_parking.area_number
WHERE st_intersects(area.geom, parking.geom)
GROUP BY area.area_number;