Блог

Мое намерение с помощью следующего оператора 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 может отображать один или несколько кортежей.

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 = 1 rownum 2

Если вы используете Oracle 12, вы можете использовать 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<>скрипка здесь