如何使用 Oracle 查询行列 A 值作为第二行列 B 的输入并循环所有关系记录

Question

在oracle查询纯SQL或PLSQL中，给定的源表是否有可能获得预期的结果？谢谢

源表：

Col_Old_ID  Col_New_ID
ID_1        ID_2
ID_2        ID_3
ID_A        ID_B
ID_B        ID_C
ID_3        ID_4
ID_4        ID_5
ID_C        ID_D

预期成果：

ID             History
ID_1           ID_1,ID_2,ID_3,ID_4,ID_5
ID_2           ID_1,ID_2,ID_3,ID_4,ID_5
ID_3           ID_1,ID_2,ID_3,ID_4,ID_5
ID_4           ID_1,ID_2,ID_3,ID_4,ID_5
ID_5           ID_1,ID_2,ID_3,ID_4,ID_5
ID_A           ID_A,ID_B,ID_C,ID_D
ID_B           ID_A,ID_B,ID_C,ID_D
ID_C           ID_A,ID_B,ID_C,ID_D
ID_D           ID_A,ID_B,ID_C,ID_D

Answer 1

SQL Fiddle

Oracle 11g R2 架构设置：

CREATE TABLE table_name ( Col_Old_ID, Col_New_ID ) AS
SELECT 'ID_1',        'ID_2' FROM DUAL UNION ALL
SELECT 'ID_2',        'ID_3' FROM DUAL UNION ALL
SELECT 'ID_A',        'ID_B' FROM DUAL UNION ALL
SELECT 'ID_B',        'ID_C' FROM DUAL UNION ALL
SELECT 'ID_3',        'ID_4' FROM DUAL UNION ALL
SELECT 'ID_4',        'ID_5' FROM DUAL UNION ALL
SELECT 'ID_C',        'ID_D' FROM DUAL;

查询 1：可以使用分层查询从当前行获取历史记录：

SELECT CONNECT_BY_ROOT( Col_Old_ID ) AS Id,
       CONNECT_BY_ROOT( Col_Old_ID )
         || SYS_CONNECT_BY_PATH( Col_New_ID, ',' ) AS History
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR Col_New_ID = Col_Old_ID

结果：

|   ID |                  HISTORY |
|------|--------------------------|
| ID_1 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_2 |      ID_2,ID_3,ID_4,ID_5 |
| ID_3 |           ID_3,ID_4,ID_5 |
| ID_4 |                ID_4,ID_5 |
| ID_A |      ID_A,ID_B,ID_C,ID_D |
| ID_B |           ID_B,ID_C,ID_D |
| ID_C |                ID_C,ID_D |

查询 2：如果您想要完整的历史记录，那么它会变得复杂，因为您需要项目前后的层次结构。

SELECT id,
       HISTORY
FROM   (
  SELECT Col_Old_ID,
         Col_new_id,
         MAX(
           SUBSTR( SYS_CONNECT_BY_PATH( Col_Old_ID, ',' ), 2 )
             || ',' || Col_New_ID
         ) OVER (
           PARTITION BY CONNECT_BY_ROOT( Col_Old_ID )
           ORDER BY     CONNECT_BY_ISLEAF DESC
         ) As history,
         CONNECT_BY_ISLEAF AS leaf
  FROM   table_name
  START WITH Col_Old_ID NOT IN ( SELECT Col_New_Id FROM table_name )
  CONNECT BY PRIOR Col_New_ID = Col_Old_ID
)
UNPIVOT ( id FOR col_type IN ( Col_Old_Id, Col_New_Id ) )
WHERE col_type = 'COL_OLD_ID'
OR    leaf = 1
ORDER BY id

结果：

|   ID |                  HISTORY |
|------|--------------------------|
| ID_1 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_2 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_3 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_4 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_5 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_A |      ID_A,ID_B,ID_C,ID_D |
| ID_B |      ID_A,ID_B,ID_C,ID_D |
| ID_C |      ID_A,ID_B,ID_C,ID_D |
| ID_D |      ID_A,ID_B,ID_C,ID_D |