对给定数据在同一个表上进行 SQL 查询 JOIN

Question

我有以下表格和数据:

CREATE TABLE TEST_TABLE (
  ID NUMBER(6) NOT NULL,
  COMMON_SEQ NUMBER(22),
  NAME VARCHAR(20),
  CONSTRAINT PK_CONST PRIMARY KEY (ID)
);
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1001, NULL, 'Michelle');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1002, NULL, 'Tiberius');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1003, NULL, 'Marigold');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1004, 999, 'Richmond');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1005, 999, 'Marianne');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1006, NULL, 'Valentin');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1007, 888, 'Juliette');
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1008, NULL, 'Lawrence');

该表中的一些记录通过 COMMON_SEQ 的公共(public)值相互关联(例如 999 的 COMMON_SEQ 与 Richmond 和 Marianne 相关)。

如何根据给定 ID 选择所有名称作为输入？

我尝试将表连接到自身(当 COMMON_SEQ 为空时工作正常)。此示例返回 Michelle 记录:

SELECT T.ID, T.COMMON_SEQ,T.NAME
FROM TEST_TABLE T 
LEFT JOIN TEST_TABLE T2 ON NOT T.COMMON_SEQ is NULL 
  AND T.COMMON_SEQ=T2.COMMON_SEQ AND T.ID<>T2.ID
WHERE T.ID=1001

但它不会返回 ID 1004 的 2 条记录。此示例仅返回 Richmond 记录(但我还需要返回 Marianne 记录):

SELECT T.ID, T.COMMON_SEQ,T.NAME
FROM TEST_TABLE T 
LEFT JOIN TEST_TABLE T2 ON NOT T.COMMON_SEQ is NULL 
  AND T.COMMON_SEQ=T2.COMMON_SEQ AND T.ID<>T2.ID
WHERE T.ID=1004

当我仅提供一个 ID 值(1004 或 1005)时，如何改进/重写查询以返回 Richmond 和 Marianne 记录？

Answer 1

您可以使用:

SELECT *
FROM TEST_TABLE t
WHERE COMMON_SEQ IN (SELECT COMMON_SEQ
                     FROM TEST_TABLE t1
                     WHERE t1.ID = 1004)
  OR t.ID = 1004;                  

<强> DBFiddle Demo

两次传递相同的参数来处理 COMMON_SEQ 中的 NULL。