postgreSQL Join，带有NOT条件的映射表

Question

有一个这样的维度表：

>td style="text-align:right；">val _b1>td style="text-align:right；">val _b2>td style="text-align:right；">val _b2>td style="text-align:right；">val_b3>td style="text-align:right；">val_b1

id	column_a	column_b
1	val_a1
2	val_a1
3	val_a2
4	val_a2
5	val_a2

您能使用case-when语句而不是联接吗？因此，您的查询/视图将是：

Select id,  
column_a,  
column_b,  
case when column_a = 'val_a1' then 'val_x1' 
when column_a = 'val_a2' and column_b = 'val_b2' then 'val_x2' 
when column_a = 'val_a2' and column_b <>'val_b2' then 'val_x3' 
else -- add your other conditions here 
end as column_x from table

如果您需要加入，您可以在SQL server上使用case when和映射表上要排除的column_b值的附加列进行一次加入。。

假设这是您的新映射表：

| column_a | column_b | column_b_exclude | column_x |
-----------|----------|------------------|----------|
| val_a1   | NULL     | NULL             | val_x1   |
| val_a2   | val_b2   | NULL             | val_x2   |
| val_a2   | NULL     | val_b2           | val_x3   |

你的加入看起来像

select * from 
table t 
inner join mapping m 
on t.column_a = m.column_a and 
case when m.column_b is null then t.column_b else m.column_b end = t.column_b and case when m.column_b_exclude is not null then m.column_b_exclude else 'x' end <> t.column_b

也许可以。请。请注意，可能有多个映射记录连接到某些维度记录。顺便说一句，在联接条件下使用OR不是很好。

select t1.id, t1.column_a, t1.column_b, t2.column_x
from _dimension t1 left outer join _mapping t2
on
t1.column_a = t2.column_a and t2.column_b = 'Any-Value' OR
t1.column_a = t2.column_a and t1.column_b = t2.column_b OR
t1.column_a = t2.column_a and t2.column_b ~* '^not ' 
and t1.column_b <> regex_replace(t2.column_b, '^not (.+)$', '1', 'i');

关于优先级，我建议您在映射表中添加一个显式的priority_level integer列。然后查询将如下所示：

select distinct on (t1.id)
t1.id, t1.column_a, t1.column_b, t2.column_x
from _dimension t1 left outer join _mapping t2
on
t1.column_a = t2.column_a and t2.column_b = 'Any-Value' OR
t1.column_a = t2.column_a and t1.column_b = t2.column_b OR
t1.column_a = t2.column_a and t2.column_b ~* '^not '
and t1.column_b <> regex_replace(t2.column_b, '^not (.+)$', '1', 'i');
order by t1.id, t2.priority_level;

为了简单起见，在映射表中使用null代替'Any-Value'和'!val_b'代替'not val_b'可能是个好主意。