I have the following table and data:
我有以下表格和数据:
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');
Some records in this table are related to each other by the common value of COMMON_SEQ (for example COMMON_SEQ of 999 relates Richmond and Marianne).
这张表中的一些记录是由COMMON_SEQ的共同值(例如,999的COMMON_SEQ,与里士满和玛丽安有关)相互关联的。
How can I select all names based on given ID as an input?
如何根据给定的ID选择所有名称作为输入?
I tried joining table to itself (works ok when COMMON_SEQ is null). This example returns Michelle record:
我尝试将表连接到它自己(当COMMON_SEQ为空时可以正常工作)。这个例子返回Michelle record:
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
But it doesn't bring back 2 records for ID 1004. This example returns only Richmond record (but I need to return also Marianne record):
但是它并没有带回两个ID 1004的记录。此示例仅返回里士满记录(但我还需要返回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
How can I improve/rewrite the query to return Richmond and Marianne records when I supply only one ID value (either 1004 or 1005)?
当我只提供一个ID值(1004或1005)时,如何改进/重写查询以返回Richmond和Marianne记录?
2 个解决方案
#1
4
You could use:
您可以使用:
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演示
Passing the same parameter twice to handle NULL
in COMMON_SEQ
.
在COMMON_SEQ中传递相同的参数两次以处理NULL。
#2
2
Try this
试试这个
SELECT COALESCE (ty.id, tx.id) AS id,
COALESCE (ty.common_seq, tx.common_seq) AS common_seq,
COALESCE (ty.name, tx.name) AS name
FROM test_table tx LEFT OUTER JOIN test_table ty
ON (tx.common_seq = ty.common_seq)
WHERE tx.ID = 1004;
With this you can avoid using IN or EXISTS and this is likely to be more performant.
有了这个,你可以避免使用IN或exist,这很可能是更高效的。
#1
4
You could use:
您可以使用:
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演示
Passing the same parameter twice to handle NULL
in COMMON_SEQ
.
在COMMON_SEQ中传递相同的参数两次以处理NULL。
#2
2
Try this
试试这个
SELECT COALESCE (ty.id, tx.id) AS id,
COALESCE (ty.common_seq, tx.common_seq) AS common_seq,
COALESCE (ty.name, tx.name) AS name
FROM test_table tx LEFT OUTER JOIN test_table ty
ON (tx.common_seq = ty.common_seq)
WHERE tx.ID = 1004;
With this you can avoid using IN or EXISTS and this is likely to be more performant.
有了这个,你可以避免使用IN或exist,这很可能是更高效的。