将一个表中的多个列中的两行与另一个表中的一行匹配。

I'm trying to figure out a way in postgres to match two columns from one of my tables to a single column in another table, without getting rid of the first tables columns. I may not explain this so well so here's an example

我试图在postgres中找到一种方法，在不删除第一个表列的情况下，将我的一个表中的两列与另一个表中的一列匹配。我可能解释不清楚，这里有个例子。

Table 1:                        Table 2:
col1    col2    col3            2col1   2col2 
1       A       B               A       13.1  
2       A       C               B       18.3
3       B       C               C       21.7
4       B       D               D       11.23

I want to get back a table where col2 and col3 can both match to what's in 2col1, and replace their values with what's in 2col2.

我想要返回一个表，其中col2和col3都可以匹配2col1中的内容，并用2col2中的内容替换它们的值。

So the result would be:

结果是:

Table 1: 
col1   col2   col3
1      13.1   18.3
2      13.1   21.7
3      18.3   21.7
4      18.3   11.23

2 个解决方案

#1

Try this:

试试这个:

SELECT t1.col1, t2.2col2 AS col2, t3.2col2 AS col3
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.col2 = t2.2col1
LEFT JOIN table2 AS t3 ON t1.col3 = t2.2col1

#2

This can help you. LEFT JOIN is your friend

这可以帮助你。左连接是你的朋友

SELECT
  t1.col1,
  t2.2col2,
  t3.2col2
FROM table1
LEFT JOIN table2 AS t2 ON t1.col2 = t2.2col1
LEFT JOIN table2 AS t3 ON t1.col3 = t3.2col1;

#1