代码测试如下:
SQL> WITH sz AS
2 (SELECT '01' ID, 4001 AS ZSPM
3 FROM DUAL
4 UNION ALL
5 SELECT '01', 4002
6 FROM DUAL
7 UNION ALL
8 SELECT '02', 5000
9 FROM DUAL
10 UNION ALL
11 SELECT '03', 5000
12 FROM DUAL
13 UNION ALL
14 SELECT '03', 4001
15 FROM DUAL
16 UNION ALL
17 SELECT '03', 4002
18 FROM DUAL),
19 dsqc AS
20 (SELECT '01' ID, 4001 AS ZSPM
21 FROM DUAL
22 UNION ALL
23 SELECT '01', 4002
24 FROM DUAL
25 UNION ALL
26 SELECT '02', 6000
27 FROM DUAL
28 UNION ALL
29 SELECT '03', 4001
30 FROM DUAL
31 UNION ALL
32 SELECT '03', 4002
33 FROM DUAL)
34 select new_id a_id,
35 max(a_zspm) a_zspm,
36 decode(count(*), 1, null, new_id) b_id,
37 max(b_zspm) b_zspm
38 from
39 (select a.id a_id,
40 a.zspm a_zspm,
41 b.id b_id,
42 b.zspm b_zspm,
43 nvl(a.id, b.id) new_id,
44 decode(a.zspm, b.zspm, null, 1) flag
45 from sz a full outer join dsqc b on a.id = b.id and a.zspm = b.zspm)
46 where flag = 1
47 group by new_id
48 order by 1
49 /
A_ID A_ZSPM B_ID B_ZSPM
---- ---------- ---- ----------
02 5000 02 6000
03 5000
SQL>