Однако заметьте, что с помощью ROWNUM = 1 можно получить первую строку. Может показаться, что это противоречит приведенному выше объяснению. Причина, почему ROWNUM = 1 обеспечивает возвращение первой строки, в том, что Oracle для определения наличия строк в таблице приходится извлекать, по крайней мере, одну из них. Внимательно проанализируйте предыдущий процесс, подставив 1 вместо 5, и вы поймете, почему для возвращения одной строки можно в качестве условия задавать ROWNUM = 1.

Возвращение n случайных записей таблицы Задача

Требуется возвратить некоторое число записей таблицы, выбранных случайным образом. Необходимо так изменить следующее выражение, чтобы при каждом последующем выполнении оно возвращало разные пять строк:

select ename, job from emp

Решение

Возьмите любую встроенную функцию, возвращающую случайные значения, которую поддерживает ваша СУБД, и примените ее в операторе ORDER BY, чтобы сортировать строки в случайном порядке. Затем с помощью описанной в предыдущем рецепте техники ограничьте число возвращаемых строк.

Используйте встроенную функцию RAND в сочетании с ORDER BY иFETCH:

1 select ename,job

2 from emp

3 order by rand() fetch first 5 rows only

MySQL

Используйте встроенную функцию RAND в сочетании с LIMIT и ORDER BY:

1 select ename,job

2 from emp

3 order by rand() limit 5

PostgreSQL

Используйте встроенную функцию RANDOM в сочетании с LIMIT иORDER BY:

1 select ename,job

2 from emp

3 order by random() limit 5

Oracle

Используйте встроенную функцию VALUE, которая находится во встроенном пакете DBMSRANDOM, в сочетании с ORDER BY и встроенную функцию ROWNUM:

1 select *

2 from (

3 select ename, job

4 from emp

6 order by dbms random.value()

8 where rownum <= 5

SQL Server

Возвращение случайного результирующего множества обеспечит использование встроенной функции NEWID в сочетании с TOP и ORDER

1 select top 5 ename,job

2 from emp

3 order by newid()

Обсуждение

Оператор ORDER BY может принимать возвращаемое функцией значение и использовать его для изменения порядка расположения элементов результирующего набора. Все предлагаемые решения ограничивают число возвращаемых строк после выполнения функции в операторе ORDER BY. Пользователям иных СУБД полезно рассмотреть решение Oracle, потому что на его примере можно (на концептуальном уровне) понять то, что в других решениях происходит за кадром .

Важно четко различать, что происходит при использовании в ORDER BY функции и числовой константы. При задании в операторе ORDER BY числовой константы сортировка осуществляется по столбцу с заданным в списке SELECT порядковым номером. Когда в ORDER BY задается функция, сортировке подвергается результат, возвращаемый функцией для каждой строки.

Поиск значений NULL Задача

Требуется найти все строки, имеющие в заданном столбце NULL (неопределенное) значение.

Решение

Чтобы выяснить, является ли значение NULL, необходимо использовать оператор IS NULL:

1 select *

2 from emp

3 where comm is null

Обсуждение

NULL никогда не бывает равен или не равен ни одному значению, даже самому себе, поэтому с помощью операторов = или != нельзя определить, равно ли значение NULL или не равно. Для проверки наличия в строке значений NULL должен использоваться оператор IS NULL. Кроме того, с помощью оператора IS NOT NULL можно выбрать строки, не содержащие NULL значения в заданном столбце.

Преобразование значений NULL в не-NULL значения Задача

Имеются строки, содержащие NULL значения, и требуется возвратить не-NULL значения вместо имеющихся NULL.

Решение

Чтобы подставить не-NULL значение вместо NULL, используйте функцию COALESCE:

1 select coalesce(comm,0)

2 from emp

Обсуждение

Функция COALESCE принимает в качестве аргументов одно или более значений. Функция возвращает первое не-NULL значение из списка. В данном решении, если значение COMM NULL, то возвращается ноль. В противном случае возвращается значение COMM.

При работе с NULL значениями лучше всего пользоваться преимуществами встроенных функций, предоставляемых вашей СУБД. Довольно часто несколько подходов могут обеспечить одинаковый результат для данной задачи. COALESCE применима во всех СУБД, как и CASE:

select case

when comm is null then 0 else comm end from emp