sql - 组合一些表，其中一些键是共同的，一些是缺失的

Question

我的 postgres 数据库(版本 9.3)中有一张土地覆盖值表。键值是“huc12code”，代表一个空间区域。这是 landcover 表的第一个条目(它有 1700 多行):

 huc12code   | open_water  |  developed  | bare_earth  |  managed | heritage
030402080205 |    0.107027 |   0.0215444 |    0.406911 |          |

管理土地和遗产土地的面积在此表中为空，但存储在单独的表中。他们还有 huc12code 作为第一列:

来自 temp_heritage 表:

 huc12code   |  heritage
-------------+-------------
030101020801 |   0.0402684

来自 temp_managed 表:

 huc12code   |   managed
-------------+------------- 
030101020802 | 0.000385026

我想将 managed 和 heritage 字段合并到我的 landcover 表中，但我看到两个问题:

1. landcover 表中的大部分 huc12code 字段都在 temp_managed 和 temp_heritage 中找到，但有些不是。这是因为“临时”表不包括 heritage 和 managed 字段等于 0 的地方。

2. 我确实希望 landcover 表包含一个 0 值，其中 heritage 和 managed 字段为 0，但由于这些值实际上并不存在于“临时”表中，我需要一些逻辑，为在 landcover 中找到的每个 huc12code 的 heritage 字段插入一个零 但不在 temp_heritage 中(管理字段也是如此)。

Answer 1

假设 huc12code 在所有表中都是唯一。

只更新值可用的行

要在单个 UPDATE 中完成所有操作，请使用 FULL [OUTER] JOIN 连接两个源表。保留所有行。然后在 UPDATE 的 FROM 子句中使用它命令:

UPDATE landcover l
SET    managed  = COALESCE(t.managed, 0) 
     , heritage = COALESCE(t.heritage, 0) 
FROM (
   SELECT huc12code, m.managed, h.heritage
   FROM   temp_managed m
   FULL   JOIN temp_heritage h USING (huc12code)
   ) t
WHERE l.huc12code = t.huc12code;

landcover 中的每一行都恰好更新一次 如果源中有一个(组合的)行。其余的完全没有动过。

更新所有行

改用两个LEFT JOIN:

UPDATE landcover l
SET    managed  = COALESCE(t.managed, 0) 
     , heritage = COALESCE(t.heritage, 0) 
FROM (
   SELECT l.huc12code, m.managed, h.heritage
   FROM   landcover l
   LEFT   JOIN temp_managed  m USING (huc12code)
   LEFT   JOIN temp_heritage h USING (huc12code)
   ) t
WHERE l.huc12code = t.huc12code;

没有源或源 NULL 值的列设置为 0。