再看一下,是否满足你的要求。
交集:
SQL> with tmp1 as(
2 select 2 a from dual
3 union all
4 select 2 from dual
5 union all
6 select 3 from dual
7 union all
8 select 4 from dual
9 union all
10 select 4 from dual
11 union all
12 select 5 from dual
13 ),
14 tmp2 as(
15 select 2 a from dual
16 union all
17 select 2 from dual
18 union all
19 select 2 from dual
20 union all
21 select 3 from dual
22 union all
23 select 3 from dual
24 union all
25 select 4 from dual
26 union all
27 select 5 from dual
28 union all
29 select 5 from dual
30 )
31 select replace(wm_concat(i),',',chr(10)) y
32 from(
33 select case when a_num < b_num
34 then (select wm_concat(a) from dual connect by rownum <= a_num)
35 else (select wm_concat(a) from dual connect by rownum <= b_num)
36 end i
37 from(
38 select a_num,x.a,b_num
39 from(select a_num,a from(
40 select count(*) a_num,a
41 from tmp1
42 group by a
43 order by a)
44 ) x,
45 (select b_num,a from(
46 select count(*) b_num,a
47 from tmp2
48 group by a
49 order by a)
50 ) y
51 where x.a = y.a
52 )
53 )
54 /
Y
------------
2
2
3
4
5
SQL>
其中,把33行的”="即可得到你的并集。