多个id上的内部连接(如果它们不是空的)
多个id上的内部连接(如果它们不是空的)
提问于 2019-12-24 17:24:54
我在创建一个捕捉以下记录的连接时遇到了一些问题。昨天我花了大约5个小时试图找出答案，但做不到。
我有两个表，表A和表B，这两个表都有以下列：
ID_1, ID_2, ID_3, ID_4 现在，我需要在两个表之间创建一个连接，以便结果在匹配的ID上提取非空的记录，如果超过1个ID匹配，则使用匹配的所有ID来提取记录，因此有以下几种情况：
场景1:两个表中的所有is都完全匹配(这很容易编写)
在这里，我将加入所有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匹配，其余为NULL (也很简单)
这里我只想加入ID_1和ID_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_1和ID_2上加入，因为ID_3和ID_4对于各自的表都是空的。
+--------+--------+--------+--------+
| 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
Stack Overflow用户
发布于 2019-12-26 01:42:19
也许你想要这样的东西？总是有人写更短的代码..。:-)
WITH directMatch AS (
SELECT
A.*
FROM
A
INNER JOIN B
-- exclude scenario 4
ON (
A.ID_1 IS NOT NULL
OR A.ID_2 IS NOT NULL
OR A.ID_3 IS NOT NULL
OR A.ID_4 IS NOT NULL
)
AND (
B.ID_1 IS NOT NULL
OR B.ID_2 IS NOT NULL
OR B.ID_3 IS NOT NULL
OR B.ID_4 IS NOT NULL
)
-- keep scenario 1+2
AND (
A.ID_1 = B.ID_1
OR A.ID_1 IS NULL AND B.ID_1 IS NULL
)
AND (
A.ID_2 = B.ID_2
OR A.ID_2 IS NULL AND B.ID_2 IS NULL
)
AND (
A.ID_3 = B.ID_3
OR A.ID_3 IS NULL AND B.ID_3 IS NULL
)
AND (
A.ID_4 = B.ID_4
OR A.ID_4 IS NULL AND B.ID_4 IS NULL
)
)
SELECT
*
FROM
-- scenario 1+2
directMatch
UNION ALL SELECT
A.*
FROM
A
INNER JOIN B
-- exclude scenario 4
ON (
A.ID_1 IS NOT NULL
OR A.ID_2 IS NOT NULL
OR A.ID_3 IS NOT NULL
OR A.ID_4 IS NOT NULL
)
AND (
B.ID_1 IS NOT NULL
OR B.ID_2 IS NOT NULL
OR B.ID_3 IS NOT NULL
OR B.ID_4 IS NOT NULL
)
-- scenario 3
AND (
COALESCE(A.ID_1, B.ID_1) = COALESCE(B.ID_1, A.ID_1)
OR A.ID_1 IS NULL AND B.ID_1 IS NULL
)
AND (
COALESCE(A.ID_2, B.ID_2) = COALESCE(B.ID_2, A.ID_2)
OR A.ID_2 IS NULL AND B.ID_2 IS NULL
)
AND (
COALESCE(A.ID_3, B.ID_3) = COALESCE(B.ID_3, A.ID_3)
OR A.ID_3 IS NULL AND B.ID_3 IS NULL
)
AND (
COALESCE(A.ID_4, B.ID_4) = COALESCE(B.ID_4, A.ID_4)
OR A.ID_4 IS NULL AND B.ID_4 IS NULL
)
AND NOT EXISTS(
SELECT
*
FROM
directMatch m
WHERE
(
A.ID_1 = m.ID_1
OR A.ID_1 IS NULL AND m.ID_1 IS NULL
)
AND (
A.ID_2 = m.ID_2
OR A.ID_2 IS NULL AND m.ID_2 IS NULL
)
AND (
A.ID_3 = m.ID_3
OR A.ID_3 IS NULL AND m.ID_3 IS NULL
)
AND (
A.ID_4 = m.ID_4
OR A.ID_4 IS NULL AND m.ID_4 IS NULL
)
)
AND NOT EXISTS(
SELECT
*
FROM
directMatch m
WHERE
(
B.ID_1 = m.ID_1
OR B.ID_1 IS NULL AND m.ID_1 IS NULL
)
AND (
B.ID_2 = m.ID_2
OR B.ID_2 IS NULL AND m.ID_2 IS NULL
)
AND (
B.ID_3 = m.ID_3
OR B.ID_3 IS NULL AND m.ID_3 IS NULL
)
AND (
B.ID_4 = m.ID_4
OR B.ID_4 IS NULL AND m.ID_4 IS NULL
)
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59471816
复制相关文章
相似问题
腾讯云开发者
