为什么返回多行,奇怪!!

显示全部楼层 · 2007-4-25 04:02:08

有表a1,和表b1,
a1格式如下
a1 a2 a3 a4 a5 a6,插入如下数据:
declare
a1 number(2) := 1;
a2 number(2) := 2;
a3 number(2) := 3;
a4 number(2) := 4;
a5 number(2) := 5;
a6 number(2) := 6;
begin
for a1 in 1 .. 28 loop
for a2 in 2 .. 29 loop
for a3 in 3 .. 30 loop
for a4 in 4 .. 31 loop

for a5 in 5 .. 32 loop

for a6 in 6 .. 33 loop

if a11;

千问 · 2007-4-25 04:02:08

没有结果,ID是唯一的..

千问 · 2007-4-25 04:02:08

其实,我是想弄个彩票的查询,就是随即产生一条结果.

千问 · 2007-4-25 04:02:08

其实楼主的问题源于dbms_random.value(...)函数.
在ORACLE的dbms_random包里的随机函数并非静态执行的,而是运行时执行的.
也就是说语句select * from temp1 where id=ceil(dbms_random.value(0,17721088))的执行
并非先执行计算ceil(dbms_random.value(0,17721088))的值R,然后找id等于R的结果集;
而是直接执行id=ceil(dbms_random.value(0,17721088))的结果集, 也既是ceil(dbms_random.value(0,17721088))会执行多次.
楼主这样的语句将得到不可预测的结果集.
例子:
with tmp as(select level-1 as id from dual connect by level <= 1e4)
--
select id
from tmp
where id = trunc(dbms_random.value(0,1e4))

千问 · 2007-4-25 04:02:08

下面是双色球红球随机(平均分布)选号的语句,希望给楼主作参考.
-- 其中tmp1为33个红球,tmp2为需要选号的注数.
with tmp1 as (select level as num from dual connect by level <= 33),
tmp2 as (select level as id from dual connect by level <= 2)
--
select
c.id as rn,
substr(sys_connect_by_path(c.num,','),2) as res
from
(select
d.id,d.num
from
(select
b.id, a.num,
row_number()over(partition by b.id order by dbms_random.random) as rn
from tmp1 a, tmp2 b) d
where d.rn <= 6)c
where level = 6
connect by level <= 6
and prior c.num < c.num
and prior c.id = c.id
[ 本帖最后由 szusunny 于 2010-11-15 11:49 编辑 ]

千问 · 2007-4-25 04:02:08

牛B呀..呵呵。学习学习!!

千问 · 2007-4-25 04:02:08

彩票，厉害