为什么这条SQL语句输出一行或多行？

Question

下面这条SQL语句的目的是从表MovieExec, Movie and Studio的连接结果中随机选择一个元组。但是这条SQL语句输出一个元组，有时输出两个或更多个元组。由于条件r = trunc(dbms_random.value(1,6))，我认为下面的SQL语句不能有两个或更多的元组。

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));

但是，如果最后一个where条件是r = (select trunc(dbms_random.value(1,6)) from dual where rownum =1 )，它将始终只输出一个元组。我想知道为什么第一个SQL语句可以显示一个或多个元组。

Answer 1

MTO已经明确展示了如何使用正确的语法重写查询。但这不是你要问的问题。您在问为什么可以获得多个行。我应该指出的是，您也可以获得0行--或者最多6行的任何数字(尽管这种情况非常少见)。

这是怎么回事？基本答案是在每一行上计算随机值。因此，考虑像这样的六行--以及生成的随机值：

  r     random
  1       5
  2       5
  3       3
  4       1
  5       2
  6       6

在本例中，第三行和第六行符合where条件。因此，您真正要做的是从前六行中选择一个随机子集，其中的随机值恰好与r匹配。

同样值得注意的是，where rownum = 1将返回一行。但是由于rownum的工作方式，where rownum = 2不返回行--因为在rownum递增为2之前必须返回rownum = 1。

Answer 2

如果你在Oracle12上，你可以使用随机排序的FETCH FIRST ROW ONLY：

SELECT e.name,
       COUNT(*) 
FROM   movie m
       INNER JOIN studio s
       ON ( m.certno = s.presno )
       INNER JOIN movieexec e
       ON (  e.producerno = m.certno )
GROUP BY
       e.name
HAVING COUNT(*) = 1
ORDER BY DBMS_RANDOM.VALUE()
FETCH FIRST ROW ONLY;

(您还可以为表指定别名并使用ANSI联接，而不是使用传统的逗号联接。)

您的查询可以重写为：

select name
from (
  select e.*,
         rownum r 
  from   (
    select e.name
    from   movie m
           INNER JOIN studio s
           ON ( m.certno = s.presno )
           INNER JOIN movieexec e
           ON ( e.producerno = m.certno )
    group by e.name
    having count(*) = 1
    order by dbms_random.value()
  ) e
)
where r = 1;

db<>fiddle