我在创建捕获以下记录的连接时遇到了一些问题。我昨天花了大约5个小时想弄明白,但是弄不明白。
我有两个表,表a和表b这两个表都有以下列:
ID_1, ID_2, ID_3, ID_4
现在,我需要在两个表之间创建一个连接,以便结果在匹配的id上提取不为null的记录,如果超过1个id匹配,那么我将使用所有匹配的id来提取记录,因此有两种情况:
场景1:两个表中的所有ID都完全匹配(这很容易编码)
在这里我会加入所有的身份证。
+--------+---------+---------+--------+
| A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
+--------+---------+---------+--------+
| CAD | AAPL | 853 | 200 |
+--------+---------+---------+--------+
+--------+--------+--------+--------+
| B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
+--------+--------+--------+--------+
| CAD | AAPL | 853 | 200 |
+--------+--------+--------+--------+
场景2:两个表中有一个或多个id匹配,其余为空(也很简单)
在这里,我将加入由身份证1和身份证3只。
+--------+--------+--------+--------+
| A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
+--------+--------+--------+--------+
| CAD | NULL | 933 | NULL |
+--------+--------+--------+--------+
+--------+--------+--------+--------+
| B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
+--------+--------+--------+--------+
| CAD | NULL | 933 | NULL |
+--------+--------+--------+--------+
场景3:表中有一个或多个id匹配,但有些不匹配
在这里,我只需要在id\u1和id\u2上进行连接,因为id\u3和id\u4对于各自的表是空的。
+--------+--------+--------+--------+
| A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
+--------+--------+--------+--------+
| CAD | TSLA | 341 | NULL |
+--------+--------+--------+--------+
+--------+--------+--------+--------+
| B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
+--------+--------+--------+--------+
| CAD | TSLA | NULL | 250 |
+--------+--------+--------+--------+
场景4:所有id都为空,因此记录被拒绝
如果表a包含以下内容:
+--------+--------+--------+--------+
| A.ID_1 | A.ID_2 | A.ID_3 | A.ID_4 |
+--------+--------+--------+--------+
| CAD | AAPL | 853 | 200 |
+--------+--------+--------+--------+
| CAD | NULL | 933 | NULL |
+--------+--------+--------+--------+
| CAD | TSLA | 341 | NULL |
+--------+--------+--------+--------+
| NULL | NULL | NULL | NULL |
+--------+--------+--------+--------+
表b包含以下内容:
+--------+--------+--------+--------+
| B.ID_1 | B.ID_2 | B.ID_3 | B.ID_4 |
+--------+--------+--------+--------+
| CAD | AAPL | 853 | 200 |
+--------+--------+--------+--------+
| CAD | NULL | 933 | NULL |
+--------+--------+--------+--------+
| CAD | TSLA | NULL | 250 |
+--------+--------+--------+--------+
| NULL | NULL | NULL | NULL |
+--------+--------+--------+--------+
结果是:
+--------+--------+--------+--------+
| ID_1 | ID_2 | ID_3 | ID_4 |
+--------+--------+--------+--------+
| CAD | AAPL | 853 | 200 |
+--------+--------+--------+--------+
| CAD | NULL | 933 | NULL |
+--------+--------+--------+--------+
| CAD | TSLA | 341 | NULL |
+--------+--------+--------+--------+
谢谢
1条答案
按热度按时间xqnpmsa81#
也许你想要这样的东西?总有人写的代码比较短…:-)