Обсуждение

Обсуждение решений, представленных в данном разделе, можно найти в Запросе 4 ; методика аналогична. В решении с использованием агрегации таблица STUDENT объединяется с таблицей TAKE, и с помощью функции COUNT в конструкции HAVING выбираются только те студенты, которые изучают два или более предметов. В решении с использованием оконной функции таблица STUDENT объединяется с таблицей TAKE, и выполняется пересчет по сегменту, заданному всеми столбцами таблицы STUDENT. После этого просто выбираются только те строки, в которых значение CNT равно или больше двух.

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

В представленном ниже решении поиск студентов, изучающих два и более предмета, осуществляется посредством рефлексивного объединения таблицы TAKE. Эквиобъединение по столбцу SNO в подзапросе гарантирует, что для каждого студента рассматриваются только изучаемые им/ею предметы. Условие больше-чем для значений CNO может выполниться, только если студент изучает более одного предмета, в противном случае CNO равнялось бы CNO (поскольку предмет всего один). Последний шаг - возвратить всех студентов, которые были возвращены подзапросом, что и показано ниже:

select * from student where sno in ( select t1.sno from take t1, take t2 where t1.sno = t2.sno and t1.cno > t2.cno

SNO SNAME AGE

1 AARON 20

3 DOUG 20

4 MAGGIE 19

6 JING 18

Запрос 7

Требуется найти студентов, которые изучают и CS112, и CS114. Кроме этих двух предметов, CS112 и CS114, студенты могут изучать и другие предметы.

Эта задача аналогична решаемой в Запросе 2 , за исключением того, что в том случае студент мог изучать более двух предметов, тогда как теперь предметов должно быть не менее 2 (только студенты AARON и DOUG изучают оба предмета, CS112 и CS114). Решение из Запроса 2

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

SNO SNAME AGE

1 AARON 20

3 DOUG 20

MySQL и PostgreSQL

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

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

2 from student s, take t

3 where s. sno = t.sno

4 and t.cno in (CS114,CS112)

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

6 having min(t.cno) != max(t.cno)

DB2, Oracle и SQL Server

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

1 select distinct sno, sname, age

2 from (

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

4 min(cno) over (partition by s.sno) as min cno,

5 max(cno) over (partition by s.sno) as max cno

6 from student s, take t

7 where s.sno = t.sno

8 and t.cno in (CS114,CS112)

9 ) x

10 where min cno != max cno

Обсуждение

В обоих решениях используется одна и та же техника. Оператор IN обеспечивает выбор только тех студентов, которые изучают CS112, или CS114, или оба предмета. Если студент не изучает оба предмета, MIN(CNO) будет равняться MAX(CNO), и данный студент будет исключен из рассмотрения. Чтобы наглядно показать, как это работает, ниже приведены промежуточные результаты решения с использованием оконной функции (T.CNO добавлен для ясности):

select s.sno, s.sname, s.age, t.cno,

min(cno) over (partition by s.sno) as min cno, max(cno) over (partition by s.sno) as max cno from student s, take t where s.sno = t.sno

and t.cno in (CS114,CS112)

SNO SNAME AGE CNO MIN C MAX C

1 AARON 20 CS114 CS112 CS114

1 AARON 20 CS112 CS112 CS114

2 CHUCK 21 CS112 CS112 CS112

3 DOUG 20 CS114 CS112 CS114

3 DOUG 20 CS112 CS112 CS114

4 MAGGIE 19 CS112 CS112 CS112

6 JING 18 CS114 CS114 CS114

Проанализировав результаты, просто заметить, что только для строк студентов AARON и DOUG выполняется условие MIN(CNO) != MAX(CNO).

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

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

select s.* from student s,

take t1, take t2

where s.sno = t1.sno

and t1.sno = t2.sno

and t1.cno = CS112

and t2.cno = CSHA

SNO SNAME AGE

1 AARON 20 3 DOUG 20

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

select s.* from take t1, student s where s.sno = t1.sno and t1.cno = CSHA and CS112 = any (select t2.cno from take t2 where t1.sno = t2.sno and t2.cno != CSHA)

SNO SNAME AGE

1 AARON 20

3 DOUG 20