求教ORACLE大牛,怎么从分组中每组随机取出特定数目的记录?

ashlv

比方说排序后为
1 a
1 s
1 d
1 f
2 g
2 h
2 j
2 k
2 l
2 q
2 w
2 e
2 r
3 t
3 y
3 u
3 i
3 o
我想每样随机取出3条得到如下结果,应该怎么做?
1 a
1 s
1 d
2 g
2 h
2 j
3 t
3 y
3 u

组的话有无数组(如 1 2 3 4 5 6 9 10 21),不要源代码级别穷举的方法,谢谢

ly5066113

使用“分析函数”
以下是DB2的SQL，Oracle 应该是类似的：

1. select
   
2.           col1
   
3.           ,col2
   
4. from (
   
5.          select
   
6.                     col1
   
7.                    ,col2
   
8.                    ,rank() over(partition by col1 order by col2) as rank
   
9.          from tab
   
10. )
    
11. where rank <= 3

复制代码

davidbeckham921

留座学习。听说sqlserver有top用法。

oracle里是不是得写个方法或者过程什么的。
rownum<4
order by ...

ashlv

回复 2# davidbeckham921


    单个还好处理,问题是现在客户要求每组随机取样80个,让我欲哭无泪,好像非得写PROC不可

magnet2008

with test as (
select 1 id ,'a' value from dual union all
select 1 id ,'s' value from dual union all
select 1 id ,'d' value from dual union all
select 1 id ,'f' value from dual union all
select 2 id ,'g' value from dual union all
select 2 id ,'h' value from dual union all
select 2 id ,'j' value from dual union all
select 2 id ,'k' value from dual union all
select 2 id ,'l' value from dual union all
select 2 id ,'q' value from dual union all
select 2 id ,'w' value from dual union all
select 2 id ,'e' value from dual union all
select 2 id ,'r' value from dual union all
select 3 id ,'t' value from dual union all
select 3 id ,'y' value from dual union all
select 3 id ,'u' value from dual union all
select 3 id ,'i' value from dual union all
select 3 id ,'o' value from dual)
select id ,value from (
select id ,value,row_number() over(partition by id order by
dbms_random.value()) rn from test) t
where t.rn<4
这样是否可用？

duolanshizhe

恩   通过row_number  结合 partition  及rownum

4楼正解

ashlv

哎,好像大家都没看懂偶的题目
结贴吧
还是直接写存储过程不用想了
谢了

renxiao2003

回复 6# duolanshizhe


    4楼明显是不正确的啊。他把数据写死的。5楼看着还有点像样。

duolanshizhe

晕  四楼那是自己构造数据

原来就是这样  五楼就是按照这个思路把表套进去了

whm11

看是不是要这种效果。

CREATE TABLE test(id NUMBER(5),name VARCHAR2(20));

INSERT INTO test VALUES(1 ,'a');
INSERT INTO test VALUES(1 ,'s');
INSERT INTO test VALUES(1 ,'d');
INSERT INTO test VALUES(1 ,'f');
INSERT INTO test VALUES(2 ,'g');
INSERT INTO test VALUES(2 ,'h');
INSERT INTO test VALUES(2 ,'j');
INSERT INTO test VALUES(2 ,'k');
INSERT INTO test VALUES(2 ,'l');
INSERT INTO test VALUES(2 ,'q');
INSERT INTO test VALUES(2 ,'w');
INSERT INTO test VALUES(2 ,'e');
INSERT INTO test VALUES(2 ,'r');
INSERT INTO test VALUES(3 ,'t');
INSERT INTO test VALUES(3 ,'y');
INSERT INTO test VALUES(3 ,'u');
INSERT INTO test VALUES(3 ,'i');
INSERT INTO test VALUES(3 ,'o');


SELECT b.id,b.name FROM
(SELECT a.id nid ,MIN(ROWNUM) nSeq FROM test a GROUP BY a.id) a,(SELECT ROWNUM nSeq1,id,name FROM test ORDER BY id) b
WHERE a.nid=b.id
AND b.nseq1<a.nseq+3
ORDER BY b.id,b.name