8 KAY 20

3 DOUG 20

Если просмотрим решение снизу вверх, мы увидим, что шаг 1, поиск всех студентов, которые старше трех или более студентов , выполняется первым. Он показан ниже (DISTINCT используется, чтобы уменьшить размер результирующего множества для удобства чтения):

select distinct s1.* 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

2 CHUCK 21 5 STEVE 22 7 BRIAN 21 10 CHAD 21

Если все эти рефлексивные объединения сбивают с толку, просто сосредоточьтесь на предикате WHERE. S1.AGE больше, чем S2.AGE, таким образом, мы знаем, что здесь рассматриваются студенты, которые старше, по крайней мере, одного студента. Далее, S2.AGE больше, чем S3.AGE. Здесь рассматриваются студенты, которые старше двух других студентов. Если на данном этапе возникают какие-то трудности, вспомните, что сравнения больше-чем транзитивны, т. е. если S1.AGE больше S2.AGE и S2.AGE больше S3.AGE, тогда S1.AGE больше S3.AGE. Чтобы лучше понять, что происходит, разложим запрос до рефлексивного объединения и затем будем постепенно достраивать его по мере понимания. Например, найдем всех студентов, которые старше, по крайней мере, одного студента (должны быть возвращены все студенты, кроме самого младшего, JING):

select distinct s1.* from student s1, student s2 where s1.age > s2.age

8 KAY 20

4 MAGGIE 19

Далее находим всех студентов, которые старше двух или более студентов (теперь и JING, и MAGGIE должны быть исключены из результирующего множества):

select distinct s1.* from student s1, student s2, student s3 where s1.age > s2.age and s2.age > s3.age

Наконец, находим всех студентов, которые старше трех или более студентов (в результирующем множестве только CHUCK, STEVE, BRIAN

и CHAD):

select distinct s1.* 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

2 CHUCK 21 5 STEVE 22 7 BRIAN 21 10 CHAD 21

Теперь, когда известно, кто из студентов старше трех или более студентов, просто возвращаем тех студентов, которые не вошли в число этих четырех, используя оператор NOT IN с подзапросом.

Запросы с условием не менее

Оборотная сторона условия не более - условие не менее . Часто задачи с не менее можно решать, применяя техники, описанные для

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

Запрос 6

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

Может быть полезным перефразировать задачу и сформулировать ее так: Найти студентов, которые изучают два или более предметов или найти студентов, которые изучают не менее двух предметов . Здесь подойдет техника, описанная в Запросе 4 : используем агрегатную функцию COUNT или оконную функцию COUNT OVER. Должно быть получено следующее результирующее множество:

SNO SNAME AGE

1 AARON 20

3 DOUG 20

4 MAGGIE 19 6 JING 18

MySQL и PostgreSQL

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

1 select s.sno,s.sname,s.age

2 from student s, take t

3 where s.sno = t.sno

4 group by s.sno,s.sname,s.age

5 having count(*) >= 2

DB2, Oracle и SQL Server

Чтобы найти студентов, изучающих, по крайней мере, два предмета, используйте оконную функцию COUNT OVER: