2015-10-14
402Запросы, которыми управляют другие запросы, называются вложенными. Прежде чем выполнить основной запрос, SQL сначала оценит внутренний запрос (подзапрос) внутри предложения WHERE. Подзапрос должен выбрать только одно поле, а тип данных этого поля должен совпадать с тем значением, с которым он будет сравниваться в предикате. Предикаты с подзапросами являются необратимыми, т.е. предикаты, включающие подзапросы, используют конструкции в следующем порядке:
< выражения > < оператор >< подзапрос >,
· В подзапросах допускается использование агрегатных функций.
· В подзапросах предложения GROUP BY или HAVING будут отклонены.
· Оператор BETWEEN, LIKE и IS NULL не могут использоваться подзапросами.
· Оператор IN позволяет использовать подзапросы, которые производят любое число строк. IN определяет набор значений предиката, одно из которых должно совпадать с другими по порядку. При использовании IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса, а значит, допускается использование IN для того, чтобы выполнить такой же подзапрос.
|
|
|
· Подзапросы всегда определяют одиночные столбцы - это обязательно, поскольку выбранный вывод сравнивается с одиночным значением.
· Команда SELECT не может использоваться в подзапросе.
· Можно использовать подзапросы внутри предложения HAVING.
· Если подзапрос не выводит никаких значений, то основной запрос также не выводит никаких значений.
Пример 1. Найти информацию об успеваемости студента Полякова, для которого известен его номер. В этом случае необходимо извлечь этот номер из таблицы с данными о студентах, и после этого применять результат к таблице успеваемости.
SELECT *
FROM USP
WHERE SNUM =
(SELECT SNUM
FROM STUDENTS
WHERE SFAM = ‘Поляков’);
Результат запроса:
UNUM OCENKA UDATE SNUM PNUM
------------------------------------------------------------------------
1001 5 10/06/1999 3412 2001
1004 4 12/06/1999 3412 2003
Пример 2. Определить какие дисциплины ведет преподаватель Викулина.
SELECT *
FROM USP
WHERE SNUM =
(SELECT DISTINCT SNUM
FROM TEACHERS
WHERE SFAM = ‘Викулина’);
Результат запроса:
PNUM PNAME TNUM HOURS COURS
-------------------------------------------------------------------
2001 Физика 4001 34 1
Пример 3. Вывести все оценки по учебным дисциплинам, значения которых выше среднего.
SELECT *
FROM USP
WHERE OCENKA>
(SELECT AVG (OCENKA)
FROM USP);
Результат запроса:
UNUM OCENKA UDATE SNUM PNUM
-----------------------------------------------------------------------
1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 2004
Пример 4. Определите условия следующего запроса.
SELECT *
FROM PREDMET
WHERE PREDMET.TNUM IN
(SELECT TEACHERS.TNUM
FROM TEACHERS
WHERE TEACHERS.TFAM
BETWEEN “ И” AND “C”);
Результат запроса:
PNUM PNAME TNUM HOURS COURS
----------------------------------------------------------------------
2002 Химия 4002 68 1
2003 Математика 4003 68 1
2005 Экономика 4005 17 3
Пример 5. Определите результат запроса.
|
|
|
SELECT *
FROM PREDMET
WHERE TNUM IN
(SELECT TNUM
FROM TEACHERS
WHERE TFAM = ‘Викулина’);
Пример 6. Найти информацию об учебном предмете, код которого на единицу меньше кода философии.
SELECT*
FROM PREDMET
WHERE PNUM =
(SELECT PNUM-1
FROM PREDMET
WHERE PNAME = ‘Философия’);
Результат запроса:
PNUM PNAME TNUM HOURS COURS
------------------------------------------------------------------
2003 Математика 4003 68 1
Пример 7. Подсчитать количество студентов с оценками выше средней, чем по дисциплине с PNUM = 2003
SELECT OCENKA, COUNT(DISTINCT SNUM)
FROM USP
GROUP BY OCENKA
HAVING OCENKA>=
(SELECT AVG (OCENKA)
FROM USP
WHERE PNUM = 2003);
Результат запроса:
OCENKA
---------------
5 2
4 2
6.2. Объединение запросов исключением подзапросов.
Внешнее объединение – это процесс объединения двух запросов, в котором второй запрос выбирает строки, исключенные первым.
Пример 1. Рассмотрим таблицу успеваемости студентов. Добавим в нее запись {1006, NULL, NULL, 3416, NULL}.Необходимо просмотреть по дисциплине, не учитывая тех, кто еще не получил оценку.
SELECT USP.SNUM, STUDENTS. SFAM,
PREDMET.PNAME, USP.OCENKA
FROM USP, STUDENTS, PREDMET
WHERE USP, STUDENTS = STUDENTS. SNUM
AND USP.SNUM= PREDMET. PNUM
UNION
SELECT USP.SNUM, STUDENTS. SFAM,
‘ НЕТ ’, 0
FROM USP, STUDENTS
WHERE USP, STUDENTS = STUDENTS. SNUM
AND NOT USP. OCENKA= ANY
(SELECT (OCENKA)
FROM USP);
ORDER BY 2 ASC;
Результат запроса:
-------------------------------------------------------------
3414 Гриценко Экономика 3
3415 Нагорный Философия 5
3416 Нагорный НЕТ 0
3417 Поляков Физика 5
3418 Поляков Математика 4
3419 Старова Математика 4
7.3. Соотнесенные подзапросы позволяют обратиться к внутреннему запросу таблицы в предложении внешнего запроса FROM, с помощью соотнесенного подзапроса. При этом подзапрос выполняется неоднократно, по одному раза для каждой записи таблицы основного запроса.
Строку внешнего запроса, для которой внутренний запрос каждый раз будет выполнятся, будем называть текущей строкой. С учетом этого, процедура оценки, выполняемой соотнесенным подзапросом:
· выбор строки из таблицы во внешнем запросе – это текущая строка;
· сохранение значение текущей строки в псевдониме, имя которого определено в предложении FROM внешнего запроса;
· выполнение подзапроса, при этом везде, где найден псевдоним из внешнего запроса, используется значение из текущей строки(это принято называть внешней ссылкой);
· оценка предиката внешнего запроса на основе результатов подзапроса;
· описанная выше последовательность повторяется для следующей строки из таблицы внешнего запроса, и так до тех пор, пока все строки не будут проверены.
Пример 1. Найти данные всех студентов, которые получали оценки 10/06/1999.
1 способ.
SELECT *
FROM STUDENT FIRST
WHERE 10/06/1999 IN
(SELECT UDATE
FROM USP SECOND
WHERE FIRST. SNUM = SECOND. SNUM);
2 способ.
SELECT STUDENTS. SNUM, STUDENTS. SFAM
STUDENTS.SIMA, STUDENTS.SOTCH,
STUDENTS.SNIP
FROM STUDENT, USP
WHERE STUDENTS. SNUM =USP. SNUM
AND USP. UDATE=10/06/1999;
Результат запроса:
SNUM SFAM SIMA SOTCH STIP
-------------------------------------------------------------------------------
3412 Поляков Анатолий Алексеевич 25.50
3413 Старова Любовь Михайловна 17.00
Пример 2. Вывести фамилии и номера всех студентов, которые получили более одной оценки.
SELECT SNUM, SFAM
FROM STUDENT FIRST
WHERE 1<
(SELECT COUNT (*)
FROM USP
WHERE SNUM = FIRST. SNUM);
Результат запроса:
SNUM SFAM
-----------------------
3412 Поляков
Часто соотнесённый подзапрос используют на основе той же самой таблицы, что и основной запрос. Это даёт возможность извлечь сложные формы информации.
Пример 3. Найти все оценки по дисциплине со значениями, выше средней по той же дисциплине.
SELECT *
FROM USP FIRST
WHERE OCENKA >
(SELECT AVG (OCENKA)
FROM USP SECOND
WHERE SECOND. PHUM = FIRST. PHUM);
Результат запроса: отсутствует.
Предложение HAVING может работать с соотнесёнными подзапросами, в этом случае предикат оценивается для каждой группы из внешнего запроса, а не для каждой строки.
Пример 4. Установить среднее значение оценок за каждый день, причем так, что это среднее должно быть больше или равно хотя бы наполовину баллов, чем минимальное значение оценки в этот день.
|
|
|
SELECT UDATE, AVG (OCENKA)
FROM USP FIRST
GROUP BY UDATE
HAVING AVG (OCENKA)>=
(SELECT MIN (OCENKA)+0.5
FROM USP SECOND
WHERE FIRST.UDATE = SECOND.UDATE);
Результат запроса:
UDATE
---------------------------
10/06/1999 4.5
12/06/1999 4.5
