and t4.cno > t5.cno

SNO SNAME AGE 3 DOUG 20 Запрос 11

Требуется найти студентов, которые старше двух (и только двух) других студентов. Эту задачу можно сформулировать иначе: требуется найти третьего самого младшего студента(ов). Должно быть получено такое результирующее множество:

SNO SNAME AGE

1 AARON 20

3 DOUG 20

8 KAY 20

9 GILLIAN 20

MySQL и PostgreSQL

Чтобы найти третьего самого младшего студента, используйте агрегатную функцию COUNT и связанный подзапрос:

1 select s1.*

2 from student s1

3 where 2 = ( select count(*)

4 from student s2

5 where s2.age < s1.age )

DB2, Oracle и SQL Server

Чтобы найти третьего самого младшего студента, используйте ранжирующую функцию DENSE RANK:

1 select sno,sname,age

2 from (

3 select sno,sname,age,

4 dense rank()over(order by age) as dr

5 from student

6 ) x

7 where dr = 3

Обсуждение

В решении с агрегатной функцией для поиска всех студентов, которые старше двух (и только двух) других студентов, используется скалярный подзапрос. Чтобы понять, что происходит, перепишем решение с использованием скалярного подзапроса. В следующем примере столбец CNT представляет, сколько студентов младше текущего:

select s1.*,

(select count(*) from student s2

where s2.age < s1.age) as cnt from student s1

Такая запись решения позволяет увидеть, кто из студентов является третьим по возрасту, начиная с самых младших (те, для которых поле

CNT равно 2).

Решение с использованием ранжирующей функции DENSE RANK аналогично решению со скалярным подзапросом в том, что все строки ранжируются на основании количества студентов, младше текущего студента (допускается повторение рангов для дублирующихся значений, и нет пропусков). Следующий запрос демонстрирует результат выполнения функции DENSE RANK:

select sno,sname,age,

dense rank()over(order by age) as dr from student

Заключительный шаг - поместить запрос во вложенный запрос и выбрать только те строки, в которых DR равно 3.

Оригинальное решение

В оригинальном решении используется двухэтапный подход: шаг 1 -находим студентов, которые старше трех или более студентов; шаг 2 -находим студентов, которые старше двух студентов и которых нет сре-

ди студентов, возвращенных в результате шага 1. Розенштейн перефразировал бы это так: Находим студентов, которые старше, по крайней мере, двух студентов и не старше, по крайней мере, трех студентов . Решение показано ниже:

select s5.* from student s5, student s6, student s7 where s5.age > s6.age and s6.age > s7.age and s5.sno not in ( select s1.sno from student s1, student s2, student s3, student s4 where s1.age > s2.age and s2.age > s3.age and s3.age > s4.age

SNO SNAME AGE

1 AARON 20 3 DOUG 20

9 GILLIAN 20 8 KAY 20

В приведенном выше решении используется техника, показанная в Запросе 5 , где подробно обсуждается, как выявлять предельные значения с помощью рефлексивных объединений.

Запросы с условием любой или все

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

Запрос 12

Требуется найти студентов, которые изучают все предметы.