Внешнее объединение с таблицей TAKE гарантирует, что в результирующее множество войдут даже студенты, не изучающие ни одного предмета. Следующий шаг - использовать функцию MAX и выбрать для каждого студента наибольшее значение, возвращенное выражением CASE. Если студент слушает курс CS112, наибольшим значением будет 1, потому что для всех остальных курсов CASE возвращает значения 0. Заключительный шаг для решения с использованием оператора GROUP BY - с помощью оператора HAVING выбрать студентов, для которых выражение MAX/CASE возвращает значение 0. В решении с использованием оконной функции необходимо поместить запрос во вложенный запрос и дать ему псевдоним TAKES CS112, потому что в предикате WHERE нельзя напрямую ссылаться на оконные функции.

11 ) x

12 where takes CS112 = 0

Обсуждение

Несмотря на различия синтаксисов, методика везде одна. Идея в том, чтобы создать в результирующем множестве столбец логического типа, значения которого являются признаком того, изучает студент CS112 или нет. Если студент изучает CS112, в этом столбце возвращается значение 1; в противном случае возвращается 0. В следующем запросе выражение CASE перенесено в список оператора SELECT. Ниже показаны промежуточные результаты:

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

case when t.cno = CS112 then 1 else 0 end as takes CS112 from student s left join take t

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

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

Оригинальное решение этой задачи очень разумно. Вот оно:

select *

from student where sno not in (select sno from take where cno = CS112)

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

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

Запрос 2

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

select *

from student where sno in ( select sno from take where cno ! = CS112 and cno != CS114 )

Из всех студентов, изучающих какие-либо предметы, только студенты DOUG и AARON слушают оба курса, CS112 и CS114. Эти двое должны быть исключены. Студент STEVE изучает CS113, но не CS112 или CS114, и тоже не должен войти в результат.

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

SNO SNAME AGE

2 CHUCK 21

4 MAGGIE 19

6 JING 18

MySQL и PostgreSQL

С помощью выражения CASE и агрегатной функции SUM найдите студентов, изучающих или CS112, или CS114, но не оба предмета одновременно:

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 sum(case when t.cno in (CS112,CS114)

6 then 1 else 0 end) = 1

DB2, Oracle и SQL Server

С помощью выражения CASE и агрегатной функции SUM OVER найдите студентов, изучающих или CS112, или CS114, но не оба предмета одновременно:

1 select distinct sno,sname,age

2 from (

3 select s.sno,s.sname,s.age,

4 sum(case when t.cno in (CS112,CS114) then 1 else 0 end)

5 over (partition by s.sno,s.sname,s.age) as takes either or

6 from student s, take t

7 where s. sno = t.sno

8 ) x

9 where takes either or = 1

Обсуждение

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

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

case when t.cno in (CS112,CS114)

then 1 else 0 end as takes either or from student s, take t where s.sno = t.sno

SNO SNAME AGE TAKES EITHER OR

1 AARON 20 1

1 AARON 20 0

1 AARON 20 1

2 CHUCK 21 1

3 DOUG 20 1

3 DOUG 20 1