sql - Oracle表中根据条件随机记录

Question

我有一个包含以下列的 Oracle 表

表结构

在查询中，我需要返回所有 CPER>=40 的记录，这很简单。但是，除了 CPER>=40 之外，我还需要为每个 CPID 列出 5 个随机记录。我附上了一份记录样本 list 。但是，在我的表中我有大约 50,000 条记录。如果您能提供帮助，我们将不胜感激。

Answer 1

甲骨文解决方案:

with CTE as
(
select t1.*, 
       row_number() over(order by DBMS_RANDOM.VALUE) as rn -- random order assigned
from MyTable t1
where CPID <40
)
select *
from CTE
where rn <=5 -- pick 5 at random

union all
select t2.*, null
from my_table t2
where CPID >= 40

SQL服务器:

with CTE as
(
select t1.*, 
       row_number() over(order by newid()) as rn -- random order assigned
from MyTable t1
where CPID <40
)
select *
from CTE
where rn <=5 -- pick 5 at random

union all
select t2.*, null
from my_table t2
where CPID >= 40