where t1.sno = t2.sno

and t2.sno = t3.sno

and t1.cno < t2.cno

and t2.cno < t3.cno )

Решение с множеством рефлексивных объединений любопытно, потому что решает задачу без использования агрегации. Чтобы понять принцип его работы, обратим внимание на предикат WHERE подзапроса. Внутренние объединения по SNO гарантируют, что во всех столбцах каждой отдельно взятой строки, которая потенциально может быть возвращена подзапросом, содержится информация одного студента. Сравнения меньше-чем выясняют, изучает ли студент более двух предметов. Предикат WHERE подзапроса можно сформулировать так: Для конкретного студента возвращаем строки, в которых первое значение CNO меньше второго значения CNO, и второе CNO меньше ТРЕТЬЕГО CNO . Если студент изучает меньше трех предметов, это выражение никогда не будет истинным, поскольку третьего CNO в этом случае нет. Задача подзапроса - найти студентов, изучающих три или более предмета. Тогда внешний запрос возвращает студентов, которые изучают, по крайней мере, один предмет и которых нет среди тех, кто был возвращен подзапросом.

Запрос 5

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

SNO SNAME AGE

6 JING 18

4 MAGGIE 19 1 AARON 20

9 GILLIAN 20

8 KAY 20

3 DOUG 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 )

Переписав решение таким образом, легко увидеть, что в окончательное результирующее множество вошли студенты, для которых значение поля CNT меньше или равно 2.

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

DENSE RANK:

select sno,sname,age,

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

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

from student

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

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

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

1. Найти множество студентов, которые старше трех или более студентов.

2. Просто возвратить всех студентов, которых нет среди выбранных на этапе 1.

Решение показано ниже:

select * from student where 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

6 JING 18

4 MAGGIE 19

1 AARON 20

9 GILLIAN 20