4 MAGGIE 19 1

4 MAGGIE 19 0

5 STEVE 22 0

6 JING 18 0 6 JING 18 1

Значение 1 в поле TAKES EITHER OR указывает на то, что студент изучает CS112 или CS114. Студент может изучать несколько предметов, поэтому следующий шаг - свести всю информацию о студенте в одну строку. Для этого используем GROUP BY с агрегатной функцией SUM. SUM просуммирует значения TAKES EITHER OR для каждого студента:

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

sum(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 group by s.sno,s.sname,s.age

Для студентов, не изучающих CS112 или CS114, значение поля

TAKES EITHER OR равно 0. Для студентов, изучающих и CS112,

и CS114, значение поля TAKES EITHER OR равно 2. Таким образом, нас интересуют только студенты со значением 1 в столбце TAKES EITHER OR. В окончательном решении для выбора таких студентов (для которых SUM по TAKES EITHER OR возвращает 1) используется оператор HAVING.

В решении с оконными функциями применяется такой же подход. Запрос придется поместить во вложенный запрос и затем ссылаться на столбец TAKES EITHER OR, поскольку в предикате WHERE нельзя обращаться к оконным функциям напрямую (при обработке SQL они рассматриваются последними, после них идет только оператор ORDER BY). Из-за принципа работы оконных функций также необходимо удалить дубликаты, возникающие в результате умножения значений.

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

Следующий запрос является оригинальным решением (внесены лишь небольшие изменения). Запрос довольно хорошо продуман и использует тот же подход, что и оригинальное решение в Запросе 1. Поиск студентов, изучающих оба предмета, CS112 и CS114, осуществляется посредством рефлексивного объединения. Затем с помощью подзапроса

Запрос 3

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

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

CHUCK - единственный студент, который изучает только CS112, и только он должен быть возвращен в результате запроса.

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

MySQL и PostgreSQL

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

1 select s.*

2 from student s,

3 take t1,

5 select sno

6 from take

7 group by sno

8 having count(*) = 1

9 ) t2

10 where s.sno = t1.sno

11 and t1.sno = t2.sno

12 and t1.cno = CS112

DB2, Oracle и SQL Server

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

эти студенты отфильтровываются из множества студентов, изучающих один из этих предметов:

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

and t.cno in ( CS112, CS114 ) and s.sno not in ( select a.sno

from take a, take b where a.sno = b.sno and a.cno = CS112 and b.cno = CS114

Обсуждение

Ключ к решению - написать запрос, отвечающий на оба вопроса: Кто из студентов изучает только один предмет? и Кто из студентов изучает CS112? . Сначала с помощью вложенного запроса T2 находим студентов, изучающих только один предмет. Следующий шаг - объединить вложенный запрос T2 с таблицей TAKE и выбрать только тех студентов, которые изучают CS112 (таким образом, получаем студентов, изучающих всего один предмет, и этот предмет - CS112). Запрос ниже обеспечивает это:

select t1.* from take t1, (

select sno from take group by sno having count(*) = 1

) t2

where t1.sno = t2.sno and t1.cno = CS112

SNO CNO

2 CS112

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

select s.sno,s.sname,s.age,t.cno, count(t.cno) over (

partition by s.sno,s.sname,s.age ) as cnt