如下:
SQL> with tmp as
2(
3select 'aaa' column1, 'bbb' column2, 'ccc' column3, 1 id from dual
4union all
5select 'aaa' column1, 'bbb' column2, 'ccc' column3, 2 id from dual
6union all
7select 'asdf' column1, 'bbb' column2, 'ffff' column3, 3 id from dual
8union all
9select 'aaa' column1, 'bbb' column2, 'ccc' column3, 4 id from dual
10union all
11select 'abc' column1, 'bcd' column2, 'sda' column3, 5 id from dual
12union all
13select 'aaa' column1, 'bbb' column2, 'ddd' column3, 6 id from dual
14union all
15select 'ccc' column1, 'bbb' column2, 'ccc' column3, 7 id from dual
16)
17selectcolumn1,
18
column2,
19
column3,
20
id
21from
22(select column1,
23
column2,
24
column3,
25
id,
26
count(*) over(partition by column1,column2,column3) cnt
27from tmp)
28where cnt > 1;
COLUMN1 COLUMN2 COLUMN3 ID
------- ------- ------- ----------
aaa bbb ccc
1
aaa bbb ccc
2
aaa bbb ccc
4
SQL>
|