假设AB相同的随机抽取一条. Ora92@Ly>SELECT * 2 FROM DD 3 WHERE (A, B) IN (SELECT A, B 4 FROM (SELECT A, B, COUNT(*) CC FROM DD GROUP BY A, B) T 5 WHERE T.CC = 1) 6 UNION 7 SELECT * 8 FROM (SELECT * 9 FROM DD 10 WHERE (A, B) IN 11 (SELECT A, B 12 FROM (SELECT A, B, COUNT(*) CC FROM DD GROUP BY A, B) T 13 WHERE T.CC > 1) 14 ORDER BY DBMS_RANDOM.RANDOM()) 15 WHERE ROWNUM = 1;A B C D ---------- ---------- ---------- ---------- A B C D A C E F C D G H已用时间: 00: 00: 00.00 Ora92@Ly>
select * from ( select t.*,row_number() over(partition by a,b order by 1) rn from 表A )where rn = 1;
Ora92@Ly>SELECT *
2 FROM DD
3 WHERE (A, B) IN (SELECT A, B
4 FROM (SELECT A, B, COUNT(*) CC FROM DD GROUP BY A, B) T
5 WHERE T.CC = 1)
6 UNION
7 SELECT *
8 FROM (SELECT *
9 FROM DD
10 WHERE (A, B) IN
11 (SELECT A, B
12 FROM (SELECT A, B, COUNT(*) CC FROM DD GROUP BY A, B) T
13 WHERE T.CC > 1)
14 ORDER BY DBMS_RANDOM.RANDOM())
15 WHERE ROWNUM = 1;A B C D
---------- ---------- ---------- ----------
A B C D
A C E F
C D G H已用时间: 00: 00: 00.00
Ora92@Ly>
select t.*,row_number() over(partition by a,b order by 1) rn from 表A
)where rn = 1;