为何指定SQL语句有时返回多行？原需求为随机返回单个元组

核心原因在于**dbms_random.value()在WHERE子句中的执行时机差异**，咱们一步步拆解清楚：

第一种写法的问题：每一行都会重新生成随机数 #

先看你的第一个查询：

select name from (
    select e.*, rownum r from (
        select movieexec.name, count(*) 
        from movieexec,studio,movie 
        where certno = presno and producerno = certno 
        group by movieexec.name having count(*) = 1
    ) e 
) where r = trunc(dbms_random.value(1,6));

当Oracle执行这个查询时，写在最外层WHERE条件里的trunc(dbms_random.value(1,6))，会被逐行调用执行——也就是说，Oracle先生成内层带r列的结果集，然后对结果里的每一行，都重新生成一个随机数，再判断当前行的r是否等于这个新生成的随机数。

举个实际场景的例子：假设内层分组后的结果有5行（r值从1到5），执行时可能出现这种情况：

• 第一行生成随机数3 → r=1≠3 → 不返回
• 第二行生成随机数2 → r=2=2 → 返回
• 第三行生成随机数3 → r=3=3 → 返回
• 第四行生成随机数5 → r=4≠5 → 不返回
• 第五行生成随机数1 → r=5≠1 → 不返回
  这时候就会返回2行，完全取决于每一行生成的随机数是否刚好和自己的r值匹配。

第二种写法的关键：只生成一次固定随机数 #

再看你的第二个查询：

select name from (
    select e.*, rownum r from (
        select movieexec.name, count(*) 
        from movieexec,studio,movie 
        where certno = presno and producerno = certno 
        group by movieexec.name having count(*) = 1
    ) e 
) where r = (select trunc(dbms_random.value(1,6)) from dual where rownum =1 );

这里的(select trunc(dbms_random.value(1,6)) from dual where rownum =1 )是一个标量子查询，Oracle会优先执行这个子查询，只生成一次随机数得到一个固定值（比如3），然后再用这个固定值去匹配外层结果集的r列。因为r是唯一的rownum值，所以最多只会找到1行匹配的数据，自然始终只返回0或1行。

额外优化建议 #

如果要更稳妥地随机取一行，还可以用更简洁的写法：通过随机排序后取第一行，逻辑更直观，也不会出现多行返回的问题：

select name 
from (
    select movieexec.name, count(*) 
    from movieexec,studio,movie 
    where certno = presno and producerno = certno 
    group by movieexec.name having count(*) = 1
)
order by dbms_random.value()
fetch first 1 rows only;

内容的提问来源于stack exchange，提问作者홍석희