table TBL_IN "IN 憑證明細表"
| 獨立身份證 | 日期 | 主題 |
|---|---|---|
| I-225 | ||
| I-300 | ||
| I-410 |
table TBL_OUT "OUT 憑證明細表"
| OID | 日期 | O主題 |
|---|---|---|
| O-20 | ||
| O-35 |
table TBL_INOUT "IN OUT 檔案連接表"
| IOID | 獨立身份證 | OID |
|---|---|---|
| 1 | I-225 | O-20 |
| 2 | I-225 | O-35 |
| 3 | I-300 | O-35 |
| 4 | I-410 | O-20 |
問題是如何在oracle sql中獲取以下查詢結果或從聯結表中訪問查詢？
查詢 QRY_INOUT_Related “為任何給定 ID 選擇所有可能相關的“雙向”值”
| ID | IO_相關 |
|---|---|
| O-20 | I-225,I410,O-35,I-300 |
| ID | IO_相關 |
|---|---|
| I-300 | O-35,I-225,O-20,I-410 |
我希望我盡可能澄清這個想法。
uj5u.com熱心網友回復：
在 Oracle 中，您似乎想要一個分層查詢，通過IIDor找到所有不同的連接OID，然后想要聚合：
SELECT id,
LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM (
SELECT DISTINCT id, value
FROM (
SELECT CONNECT_BY_ROOT oid AS id,
iid,
oid
FROM tbl_inout
START WITH oid = 'O-20'
CONNECT BY NOCYCLE
PRIOR oid = oid
OR PRIOR iid = iid
)
UNPIVOT(value FOR key IN (iid, oid))
WHERE id <> value
)
GROUP BY id
輸出：
ID IO_RELATED O-20 I-225,I-300,I-410,O-35
和
SELECT id,
LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM (
SELECT DISTINCT id, value
FROM (
SELECT CONNECT_BY_ROOT iid AS id,
iid,
oid
FROM tbl_inout
START WITH iid = 'I-300'
CONNECT BY NOCYCLE
PRIOR oid = oid
OR PRIOR iid = iid
)
UNPIVOT(value FOR key IN (iid, oid))
WHERE id <> value
)
GROUP BY id
輸出：
ID IO_RELATED I-300 I-225,I-410,O-20,O-35
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/425304.html