如何将id列表放到单独的列中

Question

CREATE TABLE master_tab (
e_id      NUMBER(10),
owner_id  NUMBER(10),
CONSTRAINT pk_master_tab PRIMARY KEY(e_id)
);
CREATE TABLE transaction_tab (
e_id      NUMBER(10),
analysis_comp  NUMBER(10),
CONSTRAINT fk_master_tab FOREIGN KEY(e_id) REFERENCES master_tab(e_id)
);
INSERT INTO master_tab VALUES(1,72);
INSERT INTO master_tab VALUES(2,72);
INSERT INTO master_tab VALUES(3,73);
INSERT INTO master_tab VALUES(4,null);
INSERT INTO transaction_tab VALUES(1,1);
INSERT INTO transaction_tab VALUES(2,1);
INSERT INTO transaction_tab VALUES(3,1);
INSERT INTO transaction_tab VALUES(4,1);

问题陈述:我有一个表master_tab，它由两列e_id和owner_id组成。我想列出属于owner_id72和73的e_id。还有另一个条件表transaction_tab中的analysis_comp标志应为1。

我的尝试:

WITH a AS(
SELECT mt.e_id FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 72
),
b AS(
SELECT mt.e_id FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 73
)
SELECT a.e_id a_eid,b.e_id b_eid FROM a
FULL OUTER JOIN b ON(b.e_id = a.e_id);

使用的工具:SQL Developer(18c)

当前输出:

+-------+-------+
| A_EID | B_EID |
+-------+-------+
| 1     | null  |
| 2     | null  |
| null  | 3     |
+-------+-------+

预期输出:

+-------+-------+
| A_EID | B_EID |
+-------+-------+
|     1 |     3 |
|     2 |       |
+-------+-------+

是否有可能只得到e_id的列表，当owner_id是72，然后在第一行它应该给所有的e_id的列表，其owner_id是72，同样，如果owner_id是73，那么它应该提供一个单独的列表e_id到另一列?如果我使用JOIN条件，它将只限制owner_id 72。是否有其他方法来处理这种情况，我们需要来自同一表的不同列的id列表

Answer 1

@Vicky，这个版本是根据你的查询修改的，可能更适合你。

WITH a AS(
SELECT mt.e_id, rownum r FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 72
),
b AS(
SELECT mt.e_id, rownum r FROM master_tab mt
JOIN transaction_tab tt ON(tt.e_id = mt.e_id AND tt.analysis_comp = 1)
WHERE mt.owner_id = 73
)
SELECT a.e_id a_eid, b.e_id b_eid from a full outer join b
on a.r=b.r ;

此版本对每个子查询使用ROWNUM，然后逐行匹配2列。

为什么我们使用row_number/rownum?因为您正在使用行号连接两列。

在SQL(或任何基于表的输出)中，通常期望一行中的数据彼此相关。你的陈述可能会让那些习惯了这一点的人感到困惑。为什么不单独输出呢?