一个随机选择记录的问题。
SQL> select id,price from t_item;
ID PRICE
---------- ----------
1 5
2 3
3 5
4 6
5 2
6 3
7 5
8 6
9 5
10 5
10 rows selected
我想，随机选择其中的3条记录，并且这3条记录的Price之和不大于15。
用过程我知道怎么做，而只用一条语句怎么写呢？
[解决办法]
先建两个临时表：
create table ty(did number,cid number,bid number)
create table t_item2(id number,price number) －－用来存放随机生成的记录
然后建立存储过程：
create or replace procedure sp_get_rows as
cursor cur_a is
select d.id did,
c.cid,
c.bid
from (select rownum n, id id, price price from t_item a) d,
(select c.n cn,
c.id cid,
c.price cp,
b.n bn,
b.id bid,
b.price bp
from (select rownum n, id id, price price from t_item a) c,
(select b.n, b.id, b.price
from (select rownum n, id id, price price
from t_item a) b
where b.n = (select floor(dbms_random.value(1,
(select count(*) + 1
from t_item))) f
from dual)) b
where c.n =
(select floor(dbms_random.value(1, b.n)) f from dual)) c
where d.n =
(select floor(dbms_random.value(c.bn + 1,
(select count(*) + 1 from t_item))) f
from dual)
and d.price + c.cp + c.bp < 15 ;
cursor cur_b(p1 number,p2 number,p3 number) is
select * from t_item where id in(p1,p2,p3);
t ty%rowtype;
i number(10);
j number(10);
k number(10);
st t_item2%rowtype;
begin
open cur_a;
fetch cur_a into t;
close cur_a;
while(t.did is null) loop
open cur_a;
fetch cur_a into t;
close cur_a;
end loop;
i:=t.did;
j:=t.cid;
k:=t.bid;
open cur_b(i,j,k);
delete from t_item2;
commit;
loop
fetch cur_b into st;
exit when cur_b%notfound;
insert into t_item2 values(st.id,st.price);
commit;
end loop;
end sp_get_rows;
执行之后，t_item2表里就会有三条随机生成的记录，并且price总和小于15。
[解决办法]
to feng2(蜀山风云)：呵呵，很久没有上csdn了哦
db2inst2@HASL> select *from xiaoxiao_t1;
ID PRICE
---------- ----------
1 5
2 3
3 5
4 6
5 2
6 3
7 5
8 6
9 5
10 5
已选择10行。
db2inst2@HASL> select * from xiaoxiao_t1
2 where instr(
3 (
4 select ', ' || id || ', '|| pre_id || ', ' ||last_id || ', ' from
5 (
6 select id, price,
7 lag(id,1,0)over(order by dbms_random.value) as pre_id,
8 lag(price,1,0)over(order by dbms_random.value) as pre_price,
9 lead(id,1,0)over(order by dbms_random.value) as last_id,
10 lead(price,1,0)over(order by dbms_random.value) as last_price
11 from xiaoxiao_t1
12 )
13 where price + pre_price + last_price <= 15
14 and pre_price <> 0 and last_price <> 0
15 and rownum = 1
16 )
17 , ', '||id|| ', ') > 0
18 ;
ID PRICE
---------- ----------
2 3
5 2
10 5
呵呵，可以随机得到的，写得不是太好，可以想点办法改改