sql - 将新值映射到 sql 查询的有效方法(即 4 列，每列具有相同的凭据)

Question

首先，我是 Postgres 的新手，所以我可能错误地描述了这个问题，所以如果已经回答，请指出我的答案。

我有一个包含 10 列的数据库。假设 4 列都使用相同的值代码(即字母 a、b、d、e)我想将查询中的所有这些重命名为它们与另一个表中的值匹配的名称。

在这个阶段我有一个很长的方法涉及嵌套的 With 语句。有没有简单的方法可以说:

表 1:

id    | col1 | col2 | col3 | col4
------+------+------+------+------
Row_1 | a    | c    | d    | e
Row_2 | c    | c    | c    | c 
Row_3 | e    | d    | d    | c

查找表:

code | value
-----+---------
a    | apple
b    | banana
c    | catfish
d    | dog
e    | egg

期望的结果:

Row_1 | apple   | catfish | dog     | egg
Row_2 | catfish | catfish | catfish | dog
Row_3 | egg     | dog     | dog     | catfish

Answer 1

是的，https://dba.stackexchange.com/questions/145038/how-to-join-to-the-same-table-multiple-times基本上应该为你工作。但是您想要一个 UPDATE，这有点不同:

无论哪种方式，lookup 表中的code 值都必须是UNIQUE，原因很明显。

更新单个列很简单:

UPDATE table1 t
SET    col1 = l.value
FROM   lookup l
WHERE  t.col1 = l.code;

如果未找到匹配项，则不会更新 table1 中的行。但是，为每一列运行单独的更新比一次更新所有列要昂贵得多——这有点棘手:

UPDATE table1 t
SET    col1 = COALESCE(l1.value, t.col1)
     , col2 = COALESCE(l2.value, t.col2)
     , col3 = COALESCE(l3.value, t.col3)
     , col4 = COALESCE(l4.value, t.col4)
FROM   table1 t1
LEFT   JOIN lookup l1 ON t.col1 = l1.code
LEFT   JOIN lookup l2 ON t.col2 = l2.code
LEFT   JOIN lookup l3 ON t.col3 = l3.code
LEFT   JOIN lookup l4 ON t.col4 = l4.code
WHERE  t1.id = t.id;

在这种情况下，所有 行都会更新。虽然 table1 中可能有 NULL 值，或者 lookup 中可能有缺失值，但我们需要 LEFT [OUTER] JOIN 来避免从更新中排除行。所以我们需要 FROM 子句中的另一个 table1 实例和 LEFT JOIN 到它。

并且添加的 COALESCE 保留未找到匹配项的每个单独列的原始值。最佳查询取决于您的问题中未披露的详细信息......

您可以添加另一个 WHERE 子句来排除没有任何变化的行。见:

• How do I (or can I) SELECT DISTINCT on multiple columns?