有表A
id value
1 101
1 102
1 103
2 201
2 203
2 204
3 301
3 302想得到的结果为
1 102
2 201
3 302
就是根据ID分组,后面的value是随机得到的,不是MIN(value)或者Max(value)
id value
1 101
1 102
1 103
2 201
2 203
2 204
3 301
3 302想得到的结果为
1 102
2 201
3 302
就是根据ID分组,后面的value是随机得到的,不是MIN(value)或者Max(value)
dbms_random.value()获取随机数
--举例
select id,ceil(dbms_random.value(min(value)-1,max(value))) value from a group by id
select id,value from(select id,value,row_number()over(partition by id order by sys_guid()) rn from a) where rn < 2;
如果是后者
SELECT ID,VALUE
FROM
(SELECT a.*,row_number()OVER(PARTITION BY ID ORDER BY dbms_random.value) rn
FROM a)
WHERE rn=1
Connected as scott
SQL>
SQL> SELECT empno,ename,deptno
2 FROM
3 (SELECT a.*,row_number()OVER(PARTITION BY deptno ORDER BY dbms_random.value) rn
4 FROM emp a)
5 WHERE rn=1
6 ;
EMPNO ENAME DEPTNO
----- ---------- ------
7839 KING 10
7876 ADAMS 20
7521 WARD 30
SQL>
SQL> SELECT empno,ename,deptno
2 FROM
3 (SELECT a.*,row_number()OVER(PARTITION BY deptno ORDER BY dbms_random.value) rn
4 FROM emp a)
5 WHERE rn=1
6 ;
EMPNO ENAME DEPTNO
----- ---------- ------
7839 KING 10
7876 ADAMS 20
7900 JAMES 30
SQL>
SQL> SELECT empno,ename,deptno
2 FROM
3 (SELECT a.*,row_number()OVER(PARTITION BY deptno ORDER BY dbms_random.value) rn
4 FROM emp a)
5 WHERE rn=1
6 ;
EMPNO ENAME DEPTNO
----- ---------- ------
7782 CLARK 10
7902 FORD 20
7844 TURNER 30
SQL> 可以啊
where b.rn=1
--目的就是随机的那就这样 借助分析函数
select *
from
(select id,value,row_number() over(partition by id order by dbms_random.value ) rn
from tb) a
where a.rn=1