oracle匹配数据,Oracle SQL显示来自同一表的匹配数据

自联接(第1-15行代表样本数据；您需要从第16行开始查询)：

SQL> with test (id, code, acct) as

2 (select

3 1, '002', 123456 from dual union all select

4 1, '004', 123456 from dual union all select

5 2, '004', 456789 from dual union all select

6 2, '004', 123456 from dual union all select

7 3, '001', 3456456 from dual union all select

8 4, '005', 975236 from dual union all select

9 5, '006', 146387 from dual union all select

10 6, '004', 0054321 from dual union all select

11 6, '010', 123456 from dual union all select

12 7, '008', 165432 from dual union all select

13 7, '007', 987654 from dual union all select

14 7, '002', 123456 from dual

15 )

16 select a.code, a.acct, a.id id1, b.id id2

17 from test a join test b on a.code = b.code

18 and a.acct = b.acct

19 and a.id > b.id

20 order by code, acct;

COD ACCT ID1 ID2

--- ---------- ---------- ----------

002 123456 7 1

004 123456 2 1

SQL>