Когда имеются наименования предметов и их количество, остается только выбрать строки, в которых значение CNT равно 1 и CNO равно CS112.

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

Оригинальное решение использует подзапрос и двойное отрицание:

select s.* from student s, take t where s.sno = t.sno

and s.sno not in ( select sno from take

where cno != CS112 )

Это исключительно разумное решение: в запросе нет ни проверки количества изучаемых студентом предметов, ни фильтра, гарантирующего, что выбранные студенты действительно изучают CS112! Но тогда, как оно работает? Подзапрос возвращает всех студентов, изучающих не CS112. Результаты показаны ниже:

select sno from take where cno != CS112

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

Если сравним два результирующих множества, мы увидим, что введение NOT IN во внешний запрос эффективно обеспечивает вычитание из множества значений SNO, возвращаемых внешним запросом, множества значений SNO, возвращенных подзапросом, в результате чего будет возвращен студент, значение SNO которого равно 2. Итак, подзапрос находит всех студентов, изучающих не CS112. Внешний запрос возвращает студентов, которых нет среди изучающих не CS112 (здесь остаются студенты, которые изучают только CS112 или вообще не изучают ни одного предмета). В результате объединения таблиц STUDENT и TAKE студенты, не изучающие ни одного предмета, отсеиваются, и остаются только те, которые изучают CS112 и только этот предмет. Решение задачи на основе множеств в лучшем виде!

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

Вопросы с условием не более представляют другой тип задач, с которыми вы сталкиваетесь время от времени. Довольно просто найти строки, для которых условие выполняется. А если требуется ограничить количество таких строк? Этому посвящены следующие два запроса.

Запрос 4

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

подзапросом. Если на мгновение опустить часть NOT IN внешнего запроса, результаты будут следующими (выбираются все студенты, изучающие что-либо):

select s.* from student s, take t where s.sno = t.sno

SNO SNAME AGE

2 CHUCK 21

3 DOUG 20

4 MAGGIE 19

5 STEVE 22

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:

Обсуждение

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

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

В своей книге Розенштейн использовал решение с агрегацией, показанное здесь для MySQL и PostgreSQL, а также предложил альтернативное решение с множеством рефлексивных объединений, которое можно увидеть ниже:

select distinct s.*

from student s, take t where s.sno = t.sno

and s.sno not in ( select t1.sno

from take t1, take t2, take t3